relative names as source data

Is there a way to use a relative named range as the source data for a series on a chart?

For example, if the name 'A' is defined as =indirect(sheet1!A1) and I type 'B' in cell A1 (and B is the name of a valid one-dimensional range), it seems like in-cell formulas will treat 'A' and 'B' the same, e.g.

=max(A)

and

=max(B)

return the same result. However, it seems like I can reference B as the source data for a series on a chart, but not A. Why wouldn't this work?

thanks,
peter

Charts using named ranges will not work from the result of the INDIRECT function.

It may be simpler to set up some helper cells that display the required values based on formula and the named range remains static.

Is this what you mean? Change the letter in cell A1 to B or D.

Originally Posted by Andy Pope

I should have been more specific.

Charts using named ranges will not work from the result of the INDIRECT function used in conjunction with the OFFSET function.

.

Hi Andy Pope

The Indirect and OFFSET function works for me. Here is an example how I'm using it. Just copy the chart to each worksheet then hit F9 or double click in any cell.

Originally Posted by Andy Pope

In your Chart.xls example change the named range B to

=OFFSET(Sheet1!$B$2,0,0,8,1)

It will still work but we need to be creative with the formulas. Here is an example below.

Originally Posted by Andy Pope

It's fine only the named ranges B and D have static range references.
It's when you change those two to be OFFSET formula that the problems arise.

I mean then second file I attach to this thread. It shows you could only have one Define name range for multiple charts. What do you take on that?

The reason I'm asking you because you have alot experience on charts and I seen your name on various forums.

Originally Posted by Andy Pope

That approachs would work well if all the chartable data was in the same location on multiple sheets.

One thing to be aware of. The CELL function is not stable when a reference is not used as the second argument.

=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)

This means all the charts on each sheet will be based on the same data set.

Try this, create a New Window and arrange Horizontally.
Show both Sheet1 and Sheet2 and then press F9. Then switch to the other view and press F9 again. You should see that both charts are running of the same data.

Nice I never knew that.


Thanks Andy Pope!