# Need Excel formula to pull data from multiple worksheets



## AndreBoston (Dec 17, 2009)

Thanks in advance for your feedback in help:

I have different products, with different prices in different areas. Each worksheet is based on the area the products are located in. Then on each worksheet, I have 2 columns, one with the product name and another with the product price.

I want a formula for a main page where I can type in the name of the product regardless of which sheet it's on and it will pull the price from the sheet that is related to the product.

I have a formula using index and match, have tried formulas using lookup, but they only work for one page. What can I do or what do I need put in the formula so that it will scan the same area on each page for the product name I input on the main page and return the price related to it?


----------



## Deckky (Dec 26, 2009)

I would create a new data worksheet that links to and pulls in all the data from the other worksheets and use the main page lookup on that. The data worksheet can then be hidden.


----------



## u112 (Jan 4, 2010)

use the vlookup function
here is an example


----------



## AndreBoston (Dec 17, 2009)

Deckky, this is what I concluded I could do from speaking with a friend who used excel. However, after we put some things together and I went to put the master sheet together, I lost all info on how to get the master sheet to pull the automatically pull info. What would I need to do to do that?


----------



## Deckky (Dec 26, 2009)

I assume the master sheet you refer to is what I called the data worksheet above. Without seeing the structure of all the worksheets I would suggest putting in a reference to the other sheets for each cell in the master sheet. To do this go to cell A1 on the new master sheet, type "=" and then click on cell A1 on the first of the worksheets with the product data. This will now pull in the data from that product sheet for cell A1 which will update if the cell contents of A1 in the product data is changed. Now copy the formula (somethng like =Sheetxxx!A1) to other cells in the master sheet so that it will pull in all the product data from the first sheet, all the other cell references will update automatically. Then at the bottom of the master sheet list click on the cell below the last item (say cell A51 if there are 50 items on the first product sheet) and do excatly the same for the all the remaining worksheets. You will end up with a list of all items and prices that will also update if any of the original data is changed. Then use the VLOOKUP function on the main page to pull in data from the master sheet according to how you want it structured. When complete you can hide the master sheet beacuse it won't need to be updated unless you need to add extra lines to the product sheets. In which case you need to do the same to the master sheet to add rows, pull in the the new data as above and change the lookup arguments accordingly on the main page.
Hope this helps.


----------

