Named Ranges

[timfouts]

Hello - I have a workbook with 2 sheets...one sheet is interactive charts and the other sheet is the data. I have alot of named ranges going on to support the charts.

Here is my problem...I need to create 2 more sheets within the same workbook to support a different set of data but same format. I copied the 2 sheets over within the workbook and the named ranges came along with it, which is what I needed...but I need the named ranges to work with the 2 new sheets within the workbook. When I did this none of my charts would work, anyone have any ideas? I dont want to create all the named ranges again for these 2 other sheets. Thanks

 

[xenou]

I don't believe its possible for data in three sheets to have the same name. To be different data, it must have a different name.

You can set sheet-level names by adding a sheet identifier:

ChartData <-- a global name (workbook level)
Sheet1!ChartData <-- a local name (worksheet level)
Sheet2!ChartData <-- another local name (worksheet level)

HTH,
ξ

 

[jolivanes]

http://www.jkp-ads.com/officemarketplacenm-en.asp

I don't know if you can use Jan Karel Pieterse's Name Manager for this.
It has a "Names Scoped To Workbook" in the Filters drop down.

Won't hurt to try though.

John

 

[timfouts]

thanks!

 

[jkpieterse]

Those named ranges, were they globally defined? If so, they are now duplicated as local names for each sheet you copied. If you are using the range names in the series formula of the chart, prepend them with the sheetname, like so:

'Sheet Name'!RangeName

To use the global range name in the SERIES formula, the syntax is like:

'Workbook name.xlsm'!RangeName