searchable dropdown lists

[Guest]

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

 

[L. Howard Kittle]

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

 

[Guest]

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>)

---