Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
searchable dropdown lists
I have a two part question.
1)how can i set up a drop down list using data validation that allows the user to start typing in the first couple letter/numbers of what they are trying to select and the list shrinks to fit those peramaters; for example if i had a list of 100 companies and someone typed S in the dropdown list, when they then clicked on the arrow only companies that start with an S appear. 2)I have a large spreadsheet Example with 200 rows and 50 columns, the columns represent companies and the rows represent product numbers that are 5 digits long. So each company/columns has a value for each row. I want the user to be able to be able to plug in the product number, then picking from the dropdown list of companies (using search in question) pick a company. I then want the corresponding value that corresponds to those two choices ie is row 2 and column 5 the i want E2 to populate into a selected cell. I know how to use vlookup but how would i go about using it for two variables. This will all be on a seperate sheet. Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
searchable dropdown lists
1. Sort the list for the dropdown and put an A at the beginning of the A's,
B at the beginning of the B's... etc throughout the list to Z. Type a letter in the dropdown cell and DO NOT hit enter. Click the arrow and you will be at the top of that letters list. (You can hit Enter, but then you have to re-select the dropdown cell) 2. Most likely an INDEX - MATCH formula. Something like this from a google search, adapted to your ranges. =INDEX(C2:E4,MATCH(A1,$C$1:$E$1,0),MATCH(A2,$B$2:$ B$4,0)) HTH Regards, Howard "pblenis" wrote in message ... I have a two part question. 1)how can i set up a drop down list using data validation that allows the user to start typing in the first couple letter/numbers of what they are trying to select and the list shrinks to fit those peramaters; for example if i had a list of 100 companies and someone typed S in the dropdown list, when they then clicked on the arrow only companies that start with an S appear. 2)I have a large spreadsheet Example with 200 rows and 50 columns, the columns represent companies and the rows represent product numbers that are 5 digits long. So each company/columns has a value for each row. I want the user to be able to be able to plug in the product number, then picking from the dropdown list of companies (using search in question) pick a company. I then want the corresponding value that corresponds to those two choices ie is row 2 and column 5 the i want E2 to populate into a selected cell. I know how to use vlookup but how would i go about using it for two variables. This will all be on a seperate sheet. Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
searchable dropdown lists
"pblenis" wrote:
I have a two part question. 1)how can i set up a drop down list using data validation that allows the user to start typing in the first couple letter/numbers of what they are trying to select and the list shrinks to fit those paramaters; for example if i had a list of 100 companies and someone typed S in the dropdown list, when they then clicked on the arrow only companies that start with an S appear. Try Debra's page at: http://www.contextures.com/xlDataVal10.html Excel -- Data Validation -- Combo box (Enable autocomplete in DV via a combo box & vba code) 2)I have a large spreadsheet Example with 200 rows and 50 columns, the columns represent companies and the rows represent product numbers that are 5 digits long. So each company/columns has a value for each row. I want the user to be able to be able to plug in the product number, then picking from the dropdown list of companies (using search in question) pick a company. I then want the corresponding value that corresponds to those two choices ie is row 2 and column 5 the i want E2 to populate into a selected cell. I know how to use vlookup but how would i go about using it for two variables. This will all be on a seperate sheet. Assume source table is in Sheet1, companies listed in B1 across, product numbers running in A2 down Create 2 defined ranges to house the companies in B1 across, and the product numbers in A2 down Click Insert Name Define then enter as Names in workbook: Refers to CompName: =Sheet1!$1:$1 Click Add then repeat for product numbers .. enter as Names in workbook: Refers to ProdNum: =Sheet1!$A:$A Click OK In Sheet2, Create 2 DVs to select Product numbers in col A, Companies in col B Select col A click Data Validation Allow: List Source: =ProdNum Click OK Repeat above to create the companies' DV droplists in col B Source: =CompName Then just place in C1: =IF(OR(A1="",B1=""),"",OFFSET(Sheet1!$A$1,MATCH(A1 ,ProdNum,0)-1,MATCH(B1,CompName,0)-1)) Copy C1 down as far as required Col C will return the intersection values from the table in Sheet1 corresponding to the product numbers selected in col A & the companies selected in col B (I'm treating your 2nd question on its own <g) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dependent dropdown lists | Excel Discussion (Misc queries) | |||
dropdown lists | Excel Worksheet Functions | |||
How to refresh dropdown lists in PivotTables? | Excel Discussion (Misc queries) | |||
Dropdown lists | Excel Discussion (Misc queries) |