Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Define chart range using indirect reference
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 |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Define chart range using indirect reference
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 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 |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Define chart range using indirect reference
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! |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Define chart range using indirect reference
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 "Thomas" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Step Chart using range names | Charts and Charting in Excel | |||
Indirect Range Referencing | Charts and Charting in Excel | |||
How do I use a name in a range reference in excel? | Charts and Charting in Excel | |||
Chart attached to dynamic range | Charts and Charting in Excel | |||
Fan charts | Charts and Charting in Excel |