dynamic range with chart

[TonyNguyen]

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.

[ConneXionLost]

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,

[Andy Pope]

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)

[TonyNguyen]

Sorry guys,

I was a bit confusing myself when trying to put it all into word.

Here are some screen shots. The first one shows that i tried to use the range name in sheet NNC, however after clicking OK button, it automatically refer to the MET data.

So my question is do I have to create different name for each sheet to refer to its own data or is there a name range where it can refer to the same cell reference on each sheet?

eg: in sheet MET I have the name "ActOR" for range A1:A10, and when I go to sheet NNC or any other sheet, i want that ActOR to refer to NNC!A1:A10 in stead of MET!A1:A10

If I have to create different name for each sheet, then is there any quick way of doing that (instead of doing Insert/Name/Define) because I will be looking at creating at least 120 names.

Thanks

[Andy Pope]

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.

Please Login or Register  to view this content.

[TonyNguyen]

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:

Please Login or Register  to view this content.

[shg]

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

Thanks.