"Workbook-Relative" Workbook-level range name

Hi,

I've been fighting for sometime with rangenames and I'm a little stuck, hopefully somebody will show me the light

I have several workbooks that have a main sheet and a reference sheet
I use a range name to refer to the reference sheet (it does a combined vertical and horizontal lookup).

As I did my testing, I realized that sheet-level rangenames cannot refer to other sheets. Is this correct?

ths I created a workbook level rangename that uses 2 cells in the main sheet to find a third one in the reference sheet


PROBLEM:
I want to copy the main sheets in a SUPERmain workbook so that formulas still work and all look in a single reference sheet

With some testing I realized that cells with formulas using workbook level range names create a reference to the original workbook, regardless if there is a workbooklevel range name with the same name

Is there some workaround?

I guess I can go inside the rangenames definitions and eliminate the letters between square brackets, but is a bit troublesome, anyone fought with that before?

thanks in advance!!

Originally Posted by romperstomper

Sheet level range names can indeed refer to other sheets.

For some reason haven't managed yet to make it work -- maybe it's because I was using sheetlevel range names of other sheets in the range name definition

anyway, when copying them to otherworkbooks will then refer to a sheet in the same workbook or try to refer to the original workbook?

Originally Posted by bagullo

For some reason haven't managed yet to make it work -- maybe it's because I was using sheetlevel range names of other sheets in the range name definition

anyway, when copying them to otherworkbooks will then refer to a sheet in the same workbook or try to refer to the original workbook?

after some testing...

Hmmm... references to other sheets are mantained on the original workbook, does not make them relative to the workbook

I guess I will have to refefine the range name when copying the sheet to the master workbook

is there some macro out there to make the "localization" of range names ? (i.e. delete the sqare brackets and the text in between from range names) can't be too hard, but looks something rather standard