Creating charts with multiple data sheets

[talula_lu]

Hello

I've looked at a variety of answers in the forums but still haven't been able to find one simple solution to this question. I have multiple data sheets between First:Last! blank sheets. The data on each sheet is in the same cell and I simply want the series in the charts to refer to the same cell between the First and Last sheet.
The number and name of the sheets that may fall between First:Last can change so I do not want to reference the series to a named sheet, hence First:Last! Is the only way I can do this through VB or can this be done any easier?
If VB is the only way, does anyone have code that can be manipulated for this?

Many thanks

 

[pgc01]

Hi
Welcome to the board

A suggestion:

Write a udf (or search one) that accepts an address like First:Last!A1 and returns an array with the values of the cells.

You define a named formula using the udf, and then use the name to load the series values like when you use dynamic ranges.

This seems to me a simple solution because you can build the chart using the worksheet interface as usual.

HTH