One (dynamic?) chart for several data sheets

[flightjock]

I have a quite large number of sheets e.g. sheets A, B, C,.... in one workbook. Each sheet contains data in a number of columns where the sheet layout is identical for all sheets. Several not necessarily adjacent columns are used to produce a chart for each sheet.
The end result is a large number of identically formatted charts each one reflecting the data in one of the worksheets.
What I would really like is one chart that can be dynamically linked to all worksheets with some kind of method to select the desired data from one of them.
Is this possible?

 

[RoryA]

You could do it a few ways:
1. With code.
2. You could have an additional sheet that the chart is linked to, and use formulas on that sheet using the INDIRECT function combined with a sheet name stored in a cell to retrieve the data from that sheet. When you change the sheet name, the data would update to reflect the new sheet name and the chart would update too.
3. You can do step 2 using defined names referencing the cell containing the sheet name - same effect, but you wouldn't need the additional worksheet (though having the additional sheet can be useful for printing the data with the chart)
HTH