Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Some sort of Lookup?
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Some sort of Lookup?
"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)) -- Allllen "Wanda" wrote: 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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Some sort of Lookup?
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 On Oct 6, 1:39 am, Wanda wrote: 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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Some sort of Lookup?
Thanks, Pete! (and Alllen, too!)
Your responses were spot on!!!! "Pete_UK" wrote: 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 On Oct 6, 1:39 am, Wanda wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need a Lookup of some sort cont. | Excel Worksheet Functions | |||
Sort then lookup | Excel Discussion (Misc queries) | |||
Sort/Filter/Lookup | Excel Discussion (Misc queries) | |||
A sort of lookup or match | Excel Worksheet Functions | |||
Lookup and Sort Question | Excel Discussion (Misc queries) |