Charting - Multiple worksheets 2

[crossface]

I have a very simple issue

I want to chart data acroos mutliple worksheets. Is this possible?

For example

WS 1 is where I have the chart

Sheets 2-5 have the same exact worksheet.

How do I chart a cell across these sheets. When I use the charting wizard I always get an Invalid reference

Thanks

Kevin Cotter
Byron Schools

 

[SkipVought]

Kevin Cotter,

This is a fundamental workbook design issue for you. Similar related data on separate sheets might be an acceptable way to DISPLAY or REPORT data to your user, but it is NOT an acceptable way to STORE the data. The data SOURCE ought not to be segmented, else it makes the kind of reporting that you want to do extremely difficult.

This only way to do what you want is to write a procedure to gather acquire the data into an array and assign the array to the appropriate SeriesCollection.

If it were me, I'd transfer ALL my data into a single table, from which I could chart or report using all the standard reporting tools in Excel

Skip,
[sub]
[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large! [/sub]

 

[pbrodsky]

I wouldn't be so hard on crossface. Freqently, the design of a spreadsheet is not under your control.

In cases like this, I usually prefer to add an additional sheet to hold the source data for the graph, and use links to the original data to make it convenient graph.

 

[SkipVought]

pbrodsky,

Except that linking sheets to combine data only works well if the ranges are static.

I'd write a simple VBA routine to suck all the data into one sheet.

I realize that often the design of a workbook has been done by someone else. A savvy programmer may be able to help his users do a better job of workbook design, by pointing out the pitfalls of design to form, which is often a barrier to function, rather then design to function from which form can be easily derived.

Skip,
[sub]
[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large! [/sub]