Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
WP
 
Posts: n/a
Default Named dynamic ranges, copied worksheets and graph source data

Good to see I'm not the only one thinking it's an area for improvement.
Jon Peltier wrote:
I've suggested it....

- Jon

WP wrote:

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

CNUK wrote:

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



  #2   Report Post  
R.VENKATARAMAN
 
Posts: n/a
Default

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

let me warn you that I am not an expert and I am still on the learning curve
regards
=========================

WP wrote in message
...
Good to see I'm not the only one thinking it's an area for improvement.
Jon Peltier wrote:
I've suggested it....

- Jon

WP wrote:

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

CNUK wrote:

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







Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 06:49 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"