Same Name refers to local ranges

M

Mats Samson

I happened to copy a worksheet with a named range.
Afterwards the named range exists in both sheets as a "local" reference,
i.e. I have the same Name in both sheets and they refer to the same cell in
the respective sheets. If you look in Insert/Define you'll find a sheet
reference to the current sheet, next to the Name.
Is there a way of creating a "local" Name reference to a cell or range,
without splitting sheets? It can be very useful, especially in vba!
Best Regards
Mats
 
K

KL

Hi Mats,

I am not sure I understand your question, but if you want a defined name
which would refer to a specific range on the sheet where it is called from,
then when defining a name in the 'Refers To:' box use the following notation
("!" will prevent Excel from automatically setting the sheet reference to
the sheet where the name has been created):

=!$A$1

Regards,
KL
 
M

Mats Samson

If you create a worksheet and name Sheet 1 to My.
Then you Define the Name Dummy to cell B2.
Copy the sheet by holding down shift and pull it rightwards and
you'll have the name My (2). Rename this sheet to Your.
If you look in cell B2 on both sheets the name Dummy exists in
both locations but seems to be "worksheet local". there is a side reference
to the sheet for the "replica" in Yours.
You can reference "Dummy" but eventual changes are only locally,
i.e made in the sheet in focus.
Can be rather useful! But I'd like to /create/use it without the awesome
copy technique!
Regards
Mats
 
D

Dave Peterson

And just in case that activesheet name needs to be enclosed in single quotes (if
it contains spaces for example):

Range("A1:B10").Name = "'" & ActiveSheet.Name & "'" & "!myRange"
 
B

Bob Phillips

Damn, I thought I had included that!

Bob

Dave Peterson said:
And just in case that activesheet name needs to be enclosed in single quotes (if
it contains spaces for example):

Range("A1:B10").Name = "'" & ActiveSheet.Name & "'" & "!myRange"
Click to expand...
 
Top