Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP/HLOOKUP to Return Several Results
I'm building a spreadsheet that will allow customers to select a product
number from a drop-down list, which will cause the components for that product to be listed underneath. For example, in cell B3 on sheet 1, they select a product number, which I want to then match to a master list on sheet 2. On sheet 2, the components (of which there are several) for that product will be listed underneath the product number: Sheet 2 (aka "master list"): A B C Product No. 52 Item #1 54769 Hex Screw Item #2 32285 Flange Item #3 12514 Washer Item #4 33696 Flexible Shunt So, sheet 2 contains these items, which are all components for Product No. 52. On sheet 1, the customer will select from a drop-down list, and upon selecting, for example, Product No. 52, the rows below will be populated with the appropriate components. Due to the manner in which this information is imported into Excel, the data on sheet 2 needs to be in this format, as opposed to listing each product in a single row. Any help would be greatly appreciated. Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP/HLOOKUP to Return Several Results
djDaemon wrote:
I'm building a spreadsheet that will allow customers to select a product number from a drop-down list, which will cause the components for that product to be listed underneath. For example, in cell B3 on sheet 1, they select a product number, which I want to then match to a master list on sheet 2. On sheet 2, the components (of which there are several) for that product will be listed underneath the product number: Sheet 2 (aka "master list"): A B C Product No. 52 Item #1 54769 Hex Screw Item #2 32285 Flange Item #3 12514 Washer Item #4 33696 Flexible Shunt So, sheet 2 contains these items, which are all components for Product No. 52. On sheet 1, the customer will select from a drop-down list, and upon selecting, for example, Product No. 52, the rows below will be populated with the appropriate components. Due to the manner in which this information is imported into Excel, the data on sheet 2 needs to be in this format, as opposed to listing each product in a single row. Any help would be greatly appreciated. Thanks! Is your part data actually organized as you show above, i.e., with product number as a "header" and part numbers underneath? If so, I think you are going to have a very hard time with this. It would be much better if /every/ part number row had a product number ID, then we could address some filtering techniques. With that said, how extensive is this list of products/parts? Could it not be the case that a Washer is a part for multiple products? You may, perhaps, need a proper relational database to do this effectively. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP/HLOOKUP to Return Several Results
Yes, natively, the data is organized like that, but we're going to go ahead
and transpose the data, so as to have unique rows for each product, similar to how a database is set up. And no, as much as I would LOVE to use a database for this application, its not going to happen. My fellow employees are just now getting accustomed to using spreadsheets, and I'm afraid a database would blow their minds. Thanks for your help! "smartin" wrote: Is your part data actually organized as you show above, i.e., with product number as a "header" and part numbers underneath? If so, I think you are going to have a very hard time with this. It would be much better if /every/ part number row had a product number ID, then we could address some filtering techniques. With that said, how extensive is this list of products/parts? Could it not be the case that a Washer is a part for multiple products? You may, perhaps, need a proper relational database to do this effectively. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP/HLOOKUP to Return Several Results
djDaemon wrote:
Yes, natively, the data is organized like that, but we're going to go ahead and transpose the data, so as to have unique rows for each product, similar to how a database is set up. And no, as much as I would LOVE to use a database for this application, its not going to happen. My fellow employees are just now getting accustomed to using spreadsheets, and I'm afraid a database would blow their minds. Thanks for your help! Ok -- do I understand correctly, you will have data organized like this? Prd -- ItemID ItemDesc 52 Item #1 12345 Hex Screw 52 Item #2 23456 Flange |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return an array of results with a vlookup | Excel Discussion (Misc queries) | |||
Can you use HLOOKUP inside VLOOKUP to tell it what column 2 return | Excel Worksheet Functions | |||
Vlookup to return results of If statement | Excel Worksheet Functions | |||
Can you link HLOOKUP AND VLOOKUP formulas and return their interce | Excel Worksheet Functions | |||
Alternative to Vlookup/Hlookup to return a value. | Excel Worksheet Functions |