I built a searchable drop down list in excel which will create quick receipts from a huge list of products.
I entered my products in one column and their respective content and price in the columns next to it.
My goal is to create a searchable drop down list which will include all of my products so that when I search and select the product my customer ordered it will ultimately pull the content and price with it to form a receipt with multiple products.
The problem I ran into is that my searchable drop down list cannot be copied to the multiple rows below.
when it comes to using an excel sheet (which is already built and rather large) for functions which are more suited to database functions.
The excel contains audiotaped medical info in columns: question physician name diagnosis/subject timecode label1 (checked box "on") label2 (checked box "off") comments
each of these are sectioned by audiotape with headers -- looking something like this -- TAPE 1 diagnosis/subject1 then under that, columns question | physician name | timecode | label1 | label2
then diagnosis/subject2 then under that columns subject/question | physician name | timecode | label1 | label2
then TAPE 2 with similar info so each TAPE has numerous DIAGNOSES covered under which there are various questions asked and answered by individual doctors. (see link to dummy file for visual) [url]
I need to be able to search on a group of criteria simultaneously: # physician’s name # subject # on-label # off-label
and return the soundbite in a user-friendly way so that the entry-level desk people (with little or no excel expertise) can retrieve it.
YES, it was not set up to do this, but this is what i've got. so i'm hoping that before i go down too many dead-ends, someone can tell me which is the best way to do this.
Looks like VLOOKUP can only really search on one column and filters would require too much expertise for entry-level employees, but should i look into VBA or Macros. or do i need to import this into Access -- or learn SQL to interface with it on a user-friendly website?
I have a spreadsheet where I am tracking several entries in a table that will keep growing. Three fields are Data Validation Drop Down Lists. The macro below works well to clear the two lists to the right when the first one is changed by the user.
[Code] .....
I want this to affect the rows below it in the table as they are added.
I need the searchable list box to return data one column to the right of the "searched" column data.
This works mostly with Data Validation.
Named range "validationlist" with "=OFFSET(Choice!$AC$4,,,COUNTIF(Choice!$AC$4:$AC$14,"?*"))"
D9 has a drop down control. Enter the cell and type "br" and click the drop down arrow. Mouse to choose an item from the list box. If you chose "brown" , it will be entered in the cell. The returned item should be "banana" from the other column. How to do this?
Once working the control needs to be used across the row in more cells: E9, F9... all the way out to W9. And there has will be a Lookup to pull data from elsewhere above D9.
This worksheet ultimately needs to be uploaded to Skydrive and used as a collaborative worksheet as a 'web app'.
I am trying to create an excel spreadsheet that calculates nutrition based on a dropdown menu. The values would change depending on the formula selected. How to I pull the data from sheet 1 and bring it in sheet 2 in order to calculate protein, carbohydrates and fat content. I have attached a copy of the excel spread sheet.
I'm trying to create a dropdown list on my "job master" sheet with reference to cells in an ODBC connection where a position "description" will give me a list of names. Then I want to be able to select another list of names individually with reference to the name I Just selected.
Staff Description Foreman
[Code].....
*I was able to do this using lists and indirect list (Personnel Sheet) but it doesn't work with my ODBC connection. I attached the excel file I am using.
Actually I plan to create an eRequisition Form for our branch sites. My focus would be the consumable items of Lexmark printers. I want to create cascading drop-down lists based on data table (worksheet : All) as per attached. From lots of examples I've seen in this or other similar forums, I notice that in order to allow this cascading to work is by creating column with header name identical with the input in the 1st List. But I'm thinking, if I need to do that, then there gonna be hundreds or maybe thousands columns to create as the number of branches are expanding, so I think tht should not be practical.
I found examples that shows how lookup 1 value can return multiple values but I totally cannot think of a way to allow those multiple values to be returned as dropdown list rather than displaying all in rows of the worksheet.
What I need is that, in 1st list, upon selecting the branch, it'll populate all items related to the branch and when selecting the item, it'll populate brand/model list for the particular item only. The input in the dropdown list should not have duplicates. Been trying using pivot to do this with macro that enables auto refresh once any changes made in the table.
create this cascading drop-down lists just based on the table as per attached? I really want to avoid having to create extra tables to allow this cascading to work.
I used this method to hide previously selected values in a drop down menu. I thought by including that value multiple times in my "employee" list, I could use it multiple times before hiding it. Is there any way to skirt this? I need to use each value three times before it disappears.
I'm trying to set up a billing form for my work. I'm trying to include drop down list for each sections (personnel, equipment, and supplies), then all sum together as a bill. I'm not sure if this is doable in excel.
I have been asked to edit a worksheet for a client, they want me to create a dependent drop list using existing data from three columns.
I have created a dynamic named range and used and index to create the drop down list, which seems to work correctly. I run into a problem with I try to add the list to more than one column, as it obviously pulls in values from the Helper sheet I have used.
When the user tries to select values in row one after making a selection in row two, the values available for selection are incorrect.
Is there any way to have a three column dependent drop list that can be used in more than one row?
I have uploaded the file here: ownCloud
The information on the tab named 'Cabling Ducts' is used in an index on the 'Helper' sheet to remove duplicate values. The drop down list columns appear in 'CS 1' under the headings 'Cable Type', 'Size (mm)' and 'Cores'. Helper sheet is self explanatory.
There are other dependent drop-down list posts on this forum, but none of them are talking about working with multiple worksheets. I have three worksheets, one called "Main", one called "Universities", one called "Schools".
One the "Main" worksheet, I have two Combo Boxes (the one from the "Form" toolbar). I would like to first pick a university from the combo box and then based on the selection, the second combo box would give me just the schools in that specific univeristy. My guess on how to link the two drop-down list is by the ID column. If I select "University of Washington" which has an ID of "WAS" it would narrow down the second list to ID with "WAS" in it (Which would be "WAS1", "WAS2, etc). The reason I am using Combo Box is because of the "Link Cell" property which give me an output of the number of the selection into a cell. I will use that to get the corresponding ID. I have attached an example to this post.
I have a drop down list, using data validation, calling from a range name on a different sheet($1). Works great. I see my list of choices.
What I want to do next is for another named range on another sheet($2) to be called when I select one of the options from the drop down.
So say the drop down is in I71, when I pick an option from that list it will populate (B72, B73:G73, B74:G74...etc...) from a named range I have already defined on ($2).
Where would I put the (if I71=Range1, then paste this info into the (B72, B73:G73, B74:G74...etc...) on $1 ?
I am trying to make an excel sheet which has a 2 column drop down. For example I want to have a drop down list in column 1 and when I select "Item A" in column 2 "item A" 's phone number appears. If i select "item B" form the drop down this, "Item B's" phone number would also appear in column 2 and so on. how this is done (if possible) on Excel 2007? I was hoping it could be done from one workbook to another but If it is easier from one worksheet to another then that is fine too.
I've made a drop down list and when I select multiple cells and choose from the drop down list, it only fills one. Here's pictures:
So in that first picture, I've selected multiple cells. But when I drop down the menu and click my selection, this happens:
Why is it that all the cells I've selected does not fill with the option that I chose from the drop down. I highly doubt that I have to manually click all the ones I want to fill with what I choose from the drop down list.
So, I need to figure out how to make the populated contents of a single dropdown box dependent upon the selected results in two other drop downs.
As there are ~35K lines in the sheet, creating Lists is impractical, and VBA is out due to client security settings.
My data sort order is: Region Name > Area # > Facility Name
I have a drop down to select the Region I want to work in.
The drop-down for Area # populates based on the selection from Region Name.
The hang-up is that the area numbers are 1-4 for each Region.
So, I can't simply populate another drop-down for Facility from the result from Area #, but it has to be based upon the results of both of the former results.
I wanted to create a multiple drop down lists (using data validation) in column B (50 in all, every 3rd line) whereby, multiple, comma deliminated, results would display in each of the cells - for use elsewhere in the spreadsheet.
I found some code (as follows) which worked perfectly for me
VB: Private Sub Worksheet_Change(ByVal Target As Range) Dim rngDV As Range Dim oldVal As String
[Code].....
As I mentioned above, I don't really understand the code and all I know is that the line about halfway down "If Target.Column = 2 Then" is defining which column (B) this will work in.
The problem I am having is that I need to protect the worksheet and the moment I "protect" the worksheet, the functionality of displaying multiple values goes away and the drop-down list reverts to only displaying one of the available values.
I have a complicated situation with Excel since I need a formula that can SUM data from the Pull 1 worksheet into the summary Sheet. I attached my sample for your reference because it is a bit complicated therefore I can not explain. It is better if you look at my Summary Sheet, need formula that can retrieve the same information.
I have a validation list drop down box set up to reference data in a defined name list. I would like to be able to select multiple values from this list to appear in one cell (preferably separated by a comma). For example, the defined name list contains the following data:
Choice 1 Choice 2 Choice 3 Choice 4
I would like to be able to populate one cell with the following via the drop down list: Choice2, Choice 4
I am hoping to create a drop down list of months in one sheet, and when I select a certain month, columns in about 10 other worksheets in the same workbook will either hide or unhide columns...
The spreadsheet is laid out with columns (C-N) for each month in the year, for actuals, then columns for budget and budget variance (O-P), then YTD Actual, YTD Budget and YTD Variance. When I select September, for example, I want October-December to hide, and leave Jan-Sep unhidden, while keeping the budget, YTD and variance columns.
I am a DJ and I want to allow my prospective customers to view my music library, while preventing my competition from printing a copy, or posting my library as their own. I think I can accomplish this by having a Java (or similar) interface on a page of my site that allows the user to search songs by title or artist. However, I am lost as to how to make that happen.
A competitor has something pretty close to what I want, but cannot get enough info about how to do it myself when viewing his source code (http://www.houstonkaraoke.com/search-pro/prodb.htm). I would like something a little more asthetically pleasing. My DB is in Excel 2000 and I am running XP.
I would like to be able to create a reactive, searchable database. I have data that will be broken down into two divisions: Origin and Destination States. I would like to type in an Origin State, then a Destination State, and then have the document provide results for matching data. For example, if company 1 is in the origin state, but not destination state, its data will not be shown in the search result. However, if company 2 is listed in both the Origin State and Destination State, its data will be listed.
Addition - Attached is a small example of the worksheet that I would like to create. In the "SEARCH" tab, I would like to enter the Origin and Destination States. the search would yield all appropriate matches. In this example, if I entered Alabama as the Orign State, and Arizona as the destination, the search would provide the following match:
In my userform I have a list of check box's that can be selected. Currently if more than one is selected, they will appear in the spread sheet in the same line one after another with a space between them. How do I make it so they either appear with a comma appearing after each, so the next value appears on the next line below or most perferably the cell turning to a drop down list with the values?
Current code
VB: If CheckBox1.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox1.Caption If CheckBox2.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox2.Caption If CheckBox3.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox3.Caption If CheckBox4.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox4.Caption If CheckBox5.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox5.Caption If CheckBox6.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox6.Caption
Spreadsheet current cell appearence if all 6 are selected ' Chinnook EH101 Lynx Puma Sea King Fixed Wing'
Required appearence: Chinnook EH101 Lynx Puma Sea King Fixed Wing
I have a column of cells with Customer info and address in each row, that I need to move into multiple columns. ie Cust name, acct #,address, city,state, zip the problem is ALL of the info for each customer is in ONE cell.To be clear, cell B1 has complete name address etc for one customer, B2 has the info for another, B3... on and on. To complicate matters as in the example, there is not a pattern in the lines. the first example shows the info beginning on the 2nd line of the cell, but the 2nd example shows info in the first line. I have tried the text to column fixed width. Didn't work because names and addresses are of varying lengths. Then I tried delimited, but there isn't a common separation between different data. I am using excel 2007. FYI I did go through many many threads and found some that were close to my problem but not close enough to work.
" JOES TOWING SERVICE [0123451] 123 STREET
CALEDONIA FL 12345 " "BUBBA / SHOP ACCURATE AUTO SUPPLY INC [0234567] 21234 HYDRAULIC DR
I have a challenge where in have a list of about 4000 rows and each cell have mutiple lines. ( i know there is a thread about this but that did not address my issue).
So my data is as follows - (I have attached teh sample file)
"Pari bom - GP and CFO 02547895452 pari@gmail.com
papa bom - GP and CFO 23365789541 papa@gmail.com
mama bom - CFO 2587412589 pari@gmail.com
nana bom - CFO 2587412589 nana@gmail.com
masi bom - CFO 2587412589 masi@gmail.com
"
I want to be in a position to seperte each line in exclusive rows and then seperate teh name, number and the email ... the later eing easier but cant get my head around seperating the cells.
