charting same cell across multiple tabs

[ronfl]

How do you create a chart reference to that it will chart the same cell
but across a range of tabs.

Example, I have a years worth of tabs on a sheet, MMDDYYY, all with the
same format on the sheet. I have a value that is in the same cell on
each tab and I want to see the trend over several months, from tab x to
tab y.

I thought this would be the formula: ='08042003:10142003'!$R$4

But I get the error: reference not valid.

Thanks, Ron.

 

[Tushar Mehta]

You cannot plot multiple cells on different worksheets as part of one x-
or y-values of one series. You will have to copy them to one sheet.
Given the naming scheme you use for the tabs, the INDIRECT and TEXT
functions should give you what you want. One *example*, where the data
are stored in a summary worksheet starting with row 1 would be

=INDIRECT(TEXT(DATE(2003,1,ROW()),"yyyymmdd")&"!A1")

This transfers data from sheet 20030101 to row 1, sheet 20030102 to row
2, etc.

Adapt to suit your own needs.

--
Regards,

Tushar Mehta
MS MVP Excel 2000-2003
www.tushar-mehta.com
Excel, PowerPoint, and VBA tutorials and add-ins
Custom Productivity Solutions leveraging MS Office