multiple worksheet filtering

[Guest]

I have 37 worksheets with 11 x 10 columns of data.

Each worksheet represents one product and the data is the test results for
the product.

I need to be able to have a chart on a worksheet that will adjust itself
based on filtered data from each product.

In other words, I only want to see the filtered data from each of the
worksheets I am drawing from.

Where should I start? Is this doable?

Thanks
Holly

 

[Bernie Deitrick]

Holly,

You could use the INDIRECT function to pull the data from sheets based on a value entered into a
cell. For example

=INDIRECT("'" & $A$1 & "'!C1")

will pull the data from cell C1 of the sheet whose name is in cell A1.

To make this into a table, use this form of the formula:

=INDIRECT("'" & $A$1 & "'!" & ADDRESS(ROW(),COLUMN()))

entered into the same relative cell as the upper left hand corner of your data tables, and copy the
formula for 11 x 10 cells. (You will need to move the sheet name if your table includes cell A1).
Then create your chart based on these formulas. When you change the value in cell A1 (or whichever
cell you use) the value will update, and the chart will change. You can also link your chart title
to cell A1's content....

HTH,
Bernie
MS Excel MVP