Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Same Name refers to local ranges
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 |
#2
|
|||
|
|||
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 "Mats Samson" wrote in message ... 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 |
#3
|
|||
|
|||
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 "KL" wrote: 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 "Mats Samson" wrote in message ... 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 |
#4
|
|||
|
|||
Mats,
This will create a local name Range("A1:B10").Name = ActiveSheet.Name & "!myRange" See also http://www.xldynamic.com/source/xld.Names.html You might also want to get a copy of Jan Karel Pieterse's N ame Mangaer, very useful when working with names. http://www.jkp-ads.com/OfficeMarketPlaceNM-EN.htm -- HTH Bob Phillips "Mats Samson" wrote in message ... 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 |
#5
|
|||
|
|||
If I remember correct if you give the range name as
sheet1!rv it becomes local name in sheet1 if you just give name as rv it becomes global try some experiments. =============== Mats Samson wrote in message ... 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 |
#6
|
|||
|
|||
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" Bob Phillips wrote: Mats, This will create a local name Range("A1:B10").Name = ActiveSheet.Name & "!myRange" See also http://www.xldynamic.com/source/xld.Names.html You might also want to get a copy of Jan Karel Pieterse's N ame Mangaer, very useful when working with names. http://www.jkp-ads.com/OfficeMarketPlaceNM-EN.htm -- HTH Bob Phillips "Mats Samson" wrote in message ... 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 -- Dave Peterson |
#7
|
|||
|
|||
Damn, I thought I had included that!
Bob "Dave Peterson" wrote in message ... 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" Bob Phillips wrote: Mats, This will create a local name Range("A1:B10").Name = ActiveSheet.Name & "!myRange" See also http://www.xldynamic.com/source/xld.Names.html You might also want to get a copy of Jan Karel Pieterse's N ame Mangaer, very useful when working with names. http://www.jkp-ads.com/OfficeMarketPlaceNM-EN.htm -- HTH Bob Phillips "Mats Samson" wrote in message ... 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple Ranges for a Chart | Charts and Charting in Excel | |||
Like 123, allow named ranges, and print named ranges | Excel Discussion (Misc queries) | |||
Problem with graph ranges | Charts and Charting in Excel | |||
compare unique identifiers in multiple ranges | Charts and Charting in Excel | |||
Named dynamic ranges, copied worksheets and graph source data | Charts and Charting in Excel |