Named dynamic ranges, copied worksheets and graph source data

C

CNUK

I have a worksheet setup that graphs data contained in two dynamic
named ranges. The graph is an object within the worksheet and not a
separate graph sheet. The named ranges are defined as follows using
the dynamic range name addin for Excel:

Pressure
='150 GPM'!$F$12:OFFSET('150 GPM'!$F$12,COUNTA('150
GPM'!$F$12:$F$65536)-1,0)

RPM
='150 GPM'!$B$12:OFFSET('150 GPM'!$B$12,COUNTA('150
GPM'!$B$12:$B$65536)-1,0)

I want to use this sheet (including the embedded graph) as a template
that can be copied to quickly process other data series.

The problem is that when the worksheet is copied, the graph source
data on the copied sheet still refers to the original sheet named
ranges. My intent was to have it update to refer to the named ranges
on the new sheet. I thought this used to work in a previous version
of Excel but it could just be my memory playing tricks on me.

Can anyone offer tips on how to get the graph source data series to
update to refer to the correct named ranges ?

Thank You
 
L

LeninVMS

CNUK,

You are facing this problem because Names are properties of a workboo
and not of the worksheet.

Do let me know if you are ok with a VBA solution for this.

Regards,
Leni
 
J

Johnny3

i can't help you on that one. im sorry. but it seems you're more skille
at this than i. could you help me out with my dilema posted just befor
yours :confused
 
W

WP

LeninVMS said:
CNUK,

You are facing this problem because Names are properties of a workbook
and not of the worksheet.

Do let me know if you are ok with a VBA solution for this.

Regards,
Lenin
VBA solution is OK with me. I was debating that, but I'm sure I had
this working in Excel 2000. I was hoping I was just having one of those
days....guess not.
 
L

LeninVMS

You could use code like this:

strPressureRange=Activeworkbook.names("Pressure").referstoR1C1

'While Iterating through all the sheets which need this change (whos
names are stored in the array strSheet)

temp=Replace(strPressureRange,"GPM 150",strSheet(i))
ActiveWorkbook.Names.Add Name:="Pressure" & i, RefersToR1C1:=temp

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Values = "=YourWorkBook.xls!Pressure
&i

You could do the same for RPM values too,


Hope this helps.

Regards,
Leni
 
J

Jon Peltier

VBA solution is OK with me. I was debating that, but I'm sure I had
this working in Excel 2000. I was hoping I was just having one of those
days....guess not.

No, it's a frustration that sheet level names as chart source data ranges are
converted to value arrays when the sheet is copied. Here's a workaround, though. The
sheet names are retained in the chart when the sheet is moved. Move the sheet to
another book (even if only temporary), close the workbook which contained the sheet,
without caving changes, then reopen the workbook and again, move the sheet where you
want it.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
W

WP

Thanks to all that replied. As usual, a lot of good information. Maybe
this could be a feature enhancement request for the next Excel.
 
J

Jon Peltier

I've suggested it....

- Jon
Thanks to all that replied. As usual, a lot of good information. Maybe
this could be a feature enhancement request for the next Excel.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top