Define chart range using indirect reference

[Thomas]

Hi,

I have an issue I doubt there is a solution for so hold on now.
I have a sheet with a number of dynamically defined data series that I
plot in a chart. Now I want to be able to multiply this sheet to use up
to 20 similar sheets in the same workbook. The thing is that I want the
references in the charts on each sheet to refer to corresponding sheet
and not to the original one. I have found a way to use the indirect
fomula combined with a VBA script to automatically define named ranges
for the series on each page. The only thing missing is to make the
charts plot these named ranges based on a similar approach using the
indirect formula (and thereby refering to the correct range names
defined in celles in each sheet). The chart series field does however
not accept the indirect formula as input.

Would really appreciate if anyone had a solution or workaround for
this!

Regards//Thomas

 

[Ed Ferrero]

Hi Thomas,

Usually, the workaround is to use indirect addressing - the worksheet
functions INDIRECT(ADRESS...)) as formulas in a worksheet range. Then
chart the worksheet range - which does not change.

If you want to use VBA to change chart ranges, I find it best to set
a range variable and use a statement like,
Chart.SeriesCollection(i).Values = myRange

There is an example at http://www.edferrero.com/charting.aspx
look at 'Chart Selector'

Ed Ferrero
http://www.edferrero.com

 

[Thomas]

Thanks for your answer but I am not quite sure it does what I need. The
ultimate solution for me would probably be if I could define the series
in the chart in a way so that they refer to the locally defined name
for the worksheet in question. When I copy the original sheet with the
defined names the defined names will turn into locally defined names
only valid for the copied sheet. The problem is that the chart on the
new sheet does not refer to these defined ranges - it refers to the
original defined ranges on the original sheet. Do you know if there is
a way to specify the series so they always refer to the local names for
the corresponding sheet?

Great wbsite you have but I didn't really find a solution for this in
there either.

Thanks for any help you have!

 

[Guest]

Thomas:
A while ago I had a couple of sheets to copy into another workbook with a
named range name that already existed (which I didnt realise). I remember
Excel popped up asking whether I would like to use the name already in the
destination sheet or to choose a new one (keeping the definition range in the
source sheet). If I remember this correctly, this might be the work around
you are looking for: you could try to copy the sheet to a new Book and copy
it back to the old one. Come to think of it a bit further, I believe it
requires that the sheet B to be copied back into sheet A should not contain
any links to sheet A, otherwise Excel is too smart and use these links (which
u don't want). Not sure whether it works in your case (your sheets may be
too complex) but possibly worth a try (Pls be careful to test it with an old
version of your sheet, just in case). Hope it solves yr problem.
Vbr,
Henk

 

[wannier]

I have the same problem!

I have made about 15 identical worksheets in a workbook, and want to copy charts between the sheets to refer to the same local cells on each sheet. I can't believe there's no solution to this in excel! It's a very painstaking process to have to go to each separate chart (about eight on each sheet) and change every data range (again, about 8 per chart). Could someone help with a VB code to circumvent this?