How to pull data from multiple worksheets based on criteria in two lists I have

Question

How to pull data from multiple worksheets based on criteria in two lists

I have a workbook with 25 worksheets pertaining to different suppliers. In each of the supplier sheets there is information that is specific to their equipment and process, but within the same category for each supplier. I need to be able to pull the description of each supplier into a comparisson table based on the users selection. I have created two lists that will give the user the option of which supplier and which piece of equipment within that supplier they would like. The probelm I am having is getting a lookup function to pull data from different worksheets and different columns within a worksheet for these two criteria. Example below

Supplier                S1         S2

Equipmet              R1         R3

Dimensions          135        88

Volume               2500      1500

Throughput           2           3

The data for these two suppliers is on two different worksheets. Also, in the case of supplier 2 they have at least 3 pieces of equipment (R3) that will be in the third colum on the data for supplier 2.

Thanks !!

Explanation / Answer

Hi..

I have assumed that the table above is in cells A1:C5, and that the tab names match the supplier names exactly. I have further assumed that your equipment names are in row 1 of each sheet, and that the properties for each piece of equipment start in row 2 and continue down the column...

In cell B3, enter the formula

=INDEX(INDIRECT("'"&B$1&"'!A1:Z100"),ROW($A2),MATCH(B$2,INDIRECT("'"&B$1&"'!1:1"),FALSE))

and copy down and across.

If any of my assumptions are untrue, post small samples of your supplier sheet layouts for the example you showed in your post.

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.