Linking Excel drop down lists

[thelma68]

I am trying to set up a billing spreadsheet. On one spreadsheet I have a
list of product codes in one column, the description in the next column and
the price in the third column. On another spreadsheet I would like to pick
the appropriate code from a drop-down list (no problem with that) and I would
like the description and price to fill in automatically once I choose the
code. I thought this would be easy but I am stuck!! Having drop down lists
in each column is not an option since there are 163 things in each drop down
- the descriptions are wordy and I want to garantee I get the right
description with the corresponding code.

I have used excel since day one but I am out of practice. Any help is
greatly appreciated.

Thanks,
cheryl

 

[L. Howard Kittle]

Try this.

On the second sheet select and name the column of product codes, perhaps
PCodes.

On the first sheet in A1 enter your drop down using List and in the Source
box enter =PCodes

Select B1 and C1 and while selected enter this formula:

=VLOOKUP($A$1,Sheet2!A163:C163,{2,3},0)

Now hit Ctrl + Shift + Enter. Excel will put curly bracket around the
formula, don't do this your self.

Select a product code from the drop down in A1 and you should get the
description and the price in B1 and C1.

If you need to change the formulas in B1 and C1 you will need to select both
cells and make the changes and use the Array - Enter method again.

HTH
Regards,
Howard
Regards

 

[grizzly6969]

say your dropdown list is on sheet 1 A1 - - - your product codes are on
sheet 3 B1:B163 and description on sheet 3 C1:C163
on sheet 1 B1 =index(sheet3!$C$1:$C$163,match(A1,sheet3!$B$1:$B$163,0))