Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Gathering data from multiple worksheets
Hi,
I have a set of worksheets (about 20) each of which contains exactly the same data, but for different products. I want to send this externally and have a summary sheet so that someone can select a product name from a drop down list and be presented with the data for that product, along with an appropriate chart (rather than have to create 20 separate charts and get them to click on worksheet tabs to see the data). I know how to create drop-down lists, and how to create the chart I want, but I don't know how to drag in the data correctly. Anyone help? Thanks, Andy |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Gathering data from multiple worksheets
Andy,
There are two approaches. The first is to use a sheet name in a cell to link all the other data calls to the sheet using INDIRECT. For example, with the sheet name "NE Sales Data" in cell D1 (without the quotes), the formula =INDIRECT("'" & D1 & "'!A1") will pull the data from cell A1 of the sheet NE Sales Data. Changing D1 to SE Sales Data will pull from that sheet. All formulas could be written with that functionality, and cell D1 could have a dropdown of all the sheet names. There are lots of possible pitfalls, but if ALL sheets are layed out identically, it is a workable if non-optimal solution. The second (and MUCH better solution) is to combine all your data into one database, and use Pivot tables and Pivot charts. Exactly how to do that depends on your sheet layout structure, and what formulas you need, but it is a generally preferred practice. HTH, Bernie MS Excel MVP "Andy F" <Andy wrote in message ... Hi, I have a set of worksheets (about 20) each of which contains exactly the same data, but for different products. I want to send this externally and have a summary sheet so that someone can select a product name from a drop down list and be presented with the data for that product, along with an appropriate chart (rather than have to create 20 separate charts and get them to click on worksheet tabs to see the data). I know how to create drop-down lists, and how to create the chart I want, but I don't know how to drag in the data correctly. Anyone help? Thanks, Andy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Gathering totals from various worksheets | Excel Worksheet Functions | |||
Gathering data from worksheet | Excel Discussion (Misc queries) | |||
gathering information from many worksheets | Excel Discussion (Misc queries) | |||
Gathering Survey data | Excel Discussion (Misc queries) | |||
gathering data | Excel Discussion (Misc queries) |