Graph across sheets, where sheet name is eg month

[Johan Myrberger]

Hi,

I have found that I can reference a specific cell across several
worksheets (eg. =SUM(Sheet1:Sheet5!A1) ).

What I would like to do is to create a chart based on a specific cell
from several worksheets. Ie name each sheet for a specific month
(2004-01, 2004-02 etc) and in eg cell A1 have the sales for that month.
Can I create a graph (bar or whatever) that have the sheet name (months)
as X-axis and the sales (A1) as Y-axis? In the best of worlds I'd like
to make the reference in a single statement like the cross-sheet summary
mentioned above.

Any hints?

regards
/Johan Myrberger

 

[Frank Kabel]

Hi
I think you need to create a helper range for this on a summary sheet
first. e.g. try
column A enter the sheet names. You could do this in your case also
with a formula in cell A1:
=TEXT(ROW(),"""2004-""00")
and copy down for 12 rows and then use in B1 the following formula
=INDIRECT("'" & A1 & "'!A1")
and copy this down as well

Now create a chart based on A1:B12