Excel named ranges, scope of definition

When I name a range on an excel spreadsheet I know that the name can be local the the one sheet I apply it to, or it can be a name that is global to all sheets. For instance I have sheet1, 2 and 3. If I name a cell on sheet1 with the name "SampleName" this name and the reference on sheet1 can be used on the other 2 sheets. I know there is a way where I can apply the same name "SampleName" on each sheet, where it refers only to the cell on the individual sheet. I can not remember how the assignment of a name and the scope of the name (global to all sheets or to one individual sheet) works. Help and Knowledge base don't lead me anywhere.

Anyone remember?

KP

 

From excel help:

3-D references If you want to analyze data in the same cell or range of cells on multiple worksheets within the workbook, use a 3-D reference. A 3-D reference includes the cell or range reference, preceded by a range of worksheet names. Excel uses any worksheets stored between the starting and ending names of the reference. For example, =SUM(Sheet2:Sheet13!B5) adds all the values contained in cell B5 on all the worksheets between and including Sheet 2 and Sheet 13

and from the same source:

Refer to the same cell or range on multiple sheets by using a 3-D reference
The workbook must contain more than one worksheet. For more guidelines for using 3-D references, click .

Click the cell where you want to enter the function.


Type = (an equal sign), enter the name of the function, and then type an opening parenthesis.


Click the tab for the first worksheet to be referenced.


Hold down SHIFT and click the tab for the last worksheet to be referenced.


Select the cell or range of cells to be referenced.


Complete the formula.


and even:

Name cells on more than one worksheet by using a 3-D reference
On the Insert menu, point to Name, and then click Define.
Show Me

In the Names in workbook box, type the name.


If the Refers to box contains a reference, select the equal sign (=) and the reference and press BACKSPACE.


In the Refers to box, type = (an equal sign).


Click the tab for the first worksheet to be referenced.


Hold down SHIFT and click the tab for the last worksheet to be referenced.


Select the cell or range of cells to be referenced.


Hope that is sufficient for your needs.

 

Y,

I am aware of the 3D naming, but that is not what I am after. In the past I have had a named range called sheet1!Name, sheet2!Name and so on. The name is specific to one sheet only, but there can be duplicate names on each sheet. To refer to this named range you need to include the sheet name it is on. Leaving the sheet name off means you are refering to the named range on that particular sheet.

Then there is also the option to have a name called "OtherName" that is available global and refers to a name in one sheet only.

It has been years I ran into this and figured it out. Even then is was obbscure in where the information was. Maybe this has changed on the newer versions and is no longer available.

There is still an indication something is left, because when you name a range the right side of the "define a name" window still shows where the named range is located.

kp

 

Ive got Excel 03 SP3

4 years from original post and i stumbled on to the same problem.

I have just worked out the answer.

I created a macro of myself defining a Name.

I looked at the code. The code says ActiveWorkbook........
I changed this to activesheet......and it worked....some example code below.

Hope this helps...its very old school and there should be a much easier way to do it.


Sub Macro1()
'
' Macro1 Macro
' Macro recorded 21/05/2009 by Reuben S Richardson
'
ActiveWorkbook.Names.Add Name:="Nc_2", RefersToR1C1:="='Loc 05'!R74C7"
ActiveWorkbook.Names.Add Name:="Nc_2", RefersToR1C1:="='Loc 05'!R74C7"
End Sub





Sub Macro1()
'
' Macro1 Macro
' Macro recorded 21/05/2009 by Reuben S Richardson
'
ActiveSheet.Names.Add Name:="Nc_2", RefersToR1C1:="='Loc 05'!R74C7"
ActiveSheet.Names.Add Name:="Nc_2", RefersToR1C1:="='Loc 05'!R74C7"
End Sub

 

Thanks for that response. It does solve the problem.

I found much later the following website:

http://www.jkp-ads.com/OfficeMarketPlaceNM-EN.asp?AllComments=True

It has a naming utility that does everything you will ever need to do with the named ranges.

KP