Named dynamic ranges, copied worksheets and graph source data

  • Thread starter Thread starter CNUK
  • Start date Start date
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
 
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
 
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
 
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
Click to expand...
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.
 
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
 
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.
Click to expand...

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/
_______
 
Thanks to all that replied. As usual, a lot of good information. Maybe
this could be a feature enhancement request for the next Excel.
 
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.
Click to expand...
 
I have a file called chart selector in my disk culled from one of the
messages from the newsgroupsl

I think that the main url is
http://edferrero.m6.net/Content/chart.html

a number of chart ideas are given and one of them is <chart selector> the
link is
http://edferrero.m6.net/Content/ChartSelector.zip
it is a zipped file and contains an example workbook.
This is for embedded charts. the procedure is an event procedure of
<thisworkbook>

as I have large number of charts which have to be updated and they are
separae sheets I could not use naming the data range (suggested By tushar
Mehta/Jon peltier) to every one of them and when I tried to prepare a vba
for naming some mistakes occured which I could not solve

I have modified the chart selector procedures for updating separate chart
sheets. Of course my procedure may be pedestrian and some expert can tune it
or modify it or
completely change it.
my customised sub is

Option Explicit



Private Sub Workbook_SheetSelectionChange(ByVal sh As Object, ByVal target
As Range)

Dim databeg As Range
Dim dataend As Range
Dim data As Range
Dim i As Integer

Set databeg = target.Offset(i, 1)
Set dataend = databeg.End(xlToRight)
Set data = Range(databeg, dataend)
Dim firstdate As Range
Dim lastdate As Range
Dim ddates As Range
Set firstdate = Range("B3")
Set lastdate = firstdate.End(xlToRight)
Set ddates = Range(firstdate, lastdate)
If target.Font.Bold = True Then
Sheets(target.Value).Select

With Charts(target.Value)
..SeriesCollection(1).Values = data
..SeriesCollection(1).XValues = ddates

.SeriesCollection(1).Name = target
ActiveChart.Axes(xlCategory).Select
With Selection.TickLabels
.Alignment = xlCenter
.Offset = 100
.Orientation = xlUpward
End With
Selection.TickLabels.NumberFormat = "dd-mmm-yy"

End With
Else
GoTo line1
End If
line1:
End Sub

before usings this event procedure dummy charts based on some data for each
of
item have to be prepared for which a vba can be written
for those interested my workbook can be sent
[email protected]
let me warn you that I am not an expert and I am still on the learning curve
regards
=========================
 
Back
Top