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

Question

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 for the help.

Answer 1

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.

Answer 2

My appologize Ashish

please try this link https://drive.google.com/file/d/0B0caLpt03I89VDZXZnVJVDRIU28/view?usp=sharing

Answer 3

Hi,

I get a message saying that you need permission.

Answer 4

I too have a similar issue and would like some assistance.

I have this workbook https://drive.google.com/open?id=0B0caLpt03I89UUFGMDhrY1RwRUk&authuser=0

In the roll-up worksheet I have used '=If' functions extensively to retrieve numbers from the other worksheets.  The workbook is 6MB in size and I would like to decrease the file size by using a better formula or function; which in hopes would improve performance.

Are you able to assist Bernie?  Or some other forum moderator?

Thank you for the assistance.

Answer 5

Worked perfectly.  Thanks for the help.