Gathering data from multiple worksheets

[Andy F]

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

 

[Bernie Deitrick]

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