Some sort of Lookup?

[Guest]

Hi all! Using Excel 2003 here, and trying to help my BF with a worksheet
problem. The file contains 5 tabs with specifications regarding 5 types of
products. For example, one tab is different brands and models of a disk
drive in column A, the speed of the drive in column B, and the capacity in
Column C. For example, Sheet "Disk Drives" contains:
A B C
1 BrandA, Model 1 certain speed x1 certain size y1
2 BrandA, Model 2 speed x2 size y2
3 BrandB, Model 7 speed x3 size y2

We're building a summary page that allows the user to choose a product from
the drop-down list that we've created from Column A of each of the 5 product
sheets, and we can't figure out how to put the other column information on
this summary sheet, once the user has made a selection from the relevant
drop-down menu. So part of we're looking for on the summary sheet is:

A B
C D
1 Disk Drives (dropDown From Sheet "drives) speed x
capacity Y



As always, any and all assistance is welcomed!

 

[Guest]

"Disk Drives" sheet
A B C
1 BrandA, Model 1 certain speed x1 certain size y1
2 BrandA, Model 2 speed x2 size y2
3 BrandB, Model 7 speed x3 size y2


"LookUp"sheet:

A1 = BrandA, Model 1 (they will select from your dropdown (data >
validation > list)

B1 = "=IF(A1="","",VLOOKUP(A1,'Disk Drives'!A:C,2,0))

C1 = "=IF(A1="","",VLOOKUP(A1,'Disk Drives'!A:C,3,0))

 

[Pete_UK]

If speed is in column C of your summary sheet, put this in C2:

=VLOOKUP(B2,'Disk Drives'!A$1:C$100,2,0)

and this in D2:

=VLOOKUP(B2,'Disk Drives'!A$1:C$100,3,0)

I've assumed you have up to 100 drives, but adjust this if you have
more. If you want to pick the sheet name up automatically from cell
A2, you can do this:

=VLOOKUP(B2,INDIRECT("'"&A2&"'!A$1:C$100",2,0)

for C2, but you must ensure that your types in column A are spelt
exactly as the sheet names are. Then you can copy this down as
required.

Hope this helps.

Pete

 

[Guest]

Thanks, Pete! (and Alllen, too!)

Your responses were spot on!!!!