source data from multiple sheets

[Guest]

i have a workbook that has a sheet for each year of data. We want to create
a rolling 15 month chart that graphs the trend over 15 months starting with
the specified month. The problem is that the information is on two different
sheets and Excel doesn't like that.

Is there anyway around this without creating a summary sheet to include both
year's info?

thanks!

 

[Jon Peltier]

http://peltiertech.com/Excel/ChartsHowTo/ChartFromDiffSheets.html

- Jon

 

[Guest]

In addition to Jon's explanation, Microsoft provides a macro approach:

http://support.microsoft.com/kb/213351/en-us

--
John Mansfield
http://cellmatrix.net

 

[Jon Peltier]

Like most MSDN macros, it's not very efficient. It also only combines
discontiguous data from one sheet, rather than multiple sheets. The approach
cannot be extended to multiple sheets, because it uses a single range to
include the discontiguous areas. It is possible to define 3D ranges that
incorporate cells from adjacent sheets, and worksheet functions can use
these ranges. A 3D range is not a valid entry for a chart, however.

- Jon