dynamic range with chart

Hi guys,

I'm trying to create a global dynamic range for my charts which works ok however the problem i have is that the range name only work for one worksheet.

Attached is the file which I have the first 2 charts with dynamic name, and when i tried to use the same range name in other sheets, i couldn't as it will refer back to the first chart range. I tried to remove the Sheet reference but still doesnt work.

Please help.

Thanks in adv.

Hi Tony,

Just guessing, but this might help. Have a read of the section on:

Global-Scope And Sheet-Scope Names

from Chip Pearson's web site.

Cheers,

I'm confused by your termonolgy. You say you want global but for it to be specific to a sheet. These descriptions are at odds with each other.

Currently your ranges are at the workbook level which means they are the same for all sheets.

If you want sheet specific sheets then you need to create sheet level named ranges.

So for the Axes example you need to create a named range,
NNC!Axes :=OFFSET(NNC!$C$6,0,0,1,MONTH(NNC!$B$6))

And use it in the series formula like this,

=SERIES(NNC!$B$7,NNC!Axes,NNC!$C$7:$O$7,1)

Named ranges can be either workbook or worksheet specific.

When you add a named range called Axes it is at the workbook level.
When you add a named range NNC!Axes it is at the worksheet level.

So if you are on the MET sheet and look at the Names dialog you will see Axes in the list.

If you are on the NNC sheet and look at the Names dialog you will see Axes with NNC to the right of Axes. This denotes a worksheet level name. If you delete it the workbook level Axes entry will remain and the NNC will be removed from the right as the worksheet level name is deleted.

Quickest way to add sheet level names is with code.

Sub CreateNames()

    Dim objName As Name
    Dim vntItem As Variant

    For Each vntItem In Array("MET", "NNC", "CSW")
        For Each objName In ActiveWorkbook.Names
            If InStr(objName.Name, "!") = 0 Then
                ActiveWorkbook.Names.Add vntItem & "!" & objName.Name, Replace(objName.Value, "MET", vntItem)
            End If
        Next
    Next
End Sub

Hi Andy,

Thanks for that, I have kinds worked out that there's no easy way to do it so I came up with a code to do all the names for me. I was having a problem of making it at the worksheet level but now you say to put the sheetname! in the front, i think that will work.

Here is my code if anyone is interested:

Sub Create_Name()
Dim s As Worksheet
Dim nm(20), n(20), f(20) As String
Dim x As Integer

'assuming you have the list of all the name and code on a separate sheet
'name is start on cell E12 and formula on cell F12

Range("E12").Select
For x = 0 To 20
If ActiveCell.Value = "" Then GoTo 100
nm(x) = ActiveCell.Value
f(x) = ActiveCell.Offset(0, 1).Formula
ActiveCell.Offset(1, 0).Select
Next x
100

'Now you have to activate the Sheet with the chart manually (currently still faster for me this way rather than thinking of some other code)

For Each s In ActiveWorkbook.Worksheets
For x = 0 To 20
If nm(x) = "" Then GoTo 200

'This will actually fix my problem :)
'==============
n(x) = s.name & "!" & nm(x)
'==============

ActiveWorkbook.Names.Add name:=n(x), RefersTo:=f(x)
Next x
200
Next s
End Sub

Tony, please take a few minutes to read the forum rules, and then edit your post to add code tags.

Thanks.