Go Back   Tech Support Forum > Microsoft Support > Microsoft Office support

Need Excel formula to pull data from multiple worksheets

This is a discussion on Need Excel formula to pull data from multiple worksheets within the Microsoft Office support forums, part of the Tech Support Forum category. Thanks in advance for your feedback in help: I have different products, with different prices in different areas. Each worksheet


Closed Thread
 
Thread Tools Search this Thread
Old 12-17-2009, 11:40 AM   #1
Registered Member
 
Join Date: Dec 2009
Posts: 2
OS: XP



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?

__________________
AndreBoston is offline  
Old 12-26-2009, 02:55 PM   #2
Registered Member
 
Join Date: Dec 2009
Location: Hampshire, UK
Posts: 11
OS: Win 7



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.

__________________
Deckky is offline  
Old 01-03-2010, 11:51 PM   #3
Registered Member
 
Join Date: Jan 2010
Posts: 4
OS: WIN 7



use the vlookup function
here is an example
Attached Files
File Type: rar example.rar (1.4 KB, 144 views)
__________________
u112 is offline  
Old 01-04-2010, 02:05 PM   #4
Registered Member
 
Join Date: Dec 2009
Posts: 2
OS: XP



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?
__________________
AndreBoston is offline  
Old 01-05-2010, 04:34 AM   #5
Registered Member
 
Join Date: Dec 2009
Location: Hampshire, UK
Posts: 11
OS: Win 7



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.

__________________
Deckky is offline  
Closed Thread

Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is on
Smilies are on
[IMG] code is on
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Post a Question


» Site Navigation
 > FAQ
  > 10.0.0.2


All times are GMT -7. The time now is 03:12 AM.


Copyright 2001 - 2014, Tech Support Forum

Windows 7 - Windows XP - Windows Vista - Trojan Removal - Spyware Removal - Virus Removal - Networking - Security - Top Web Hosts