Cell Naming

  • Thread starter Thread starter Bruce A. Julseth
  • Start date Start date
B

Bruce A. Julseth

Can I use a name twice in a workbook, only in different sheets? That is to
say can I have Name "Area1" for "Sheet 1!$A$1:$F$10" and also have Name
"Area!" for "Sheet2!$A$1:$F$14"

I tried the above but it didn't work. I'm not sure if it can't be done or I
did something wrong.

Thanks for the help..
 
Yes.

When you name the range--either by typing in the namebox (to the left of the
formula bar) or by using the Insert|name dialog, make sure you include the name
of the sheet.

'Sheet 1'!Area1

(since "Sheet 1" (with the space character) requires the name be in single
quotes/apostrophes.)
 
It has been my experience with xl2003 that when you type in the name in
either place, IF you are on the desired sheet, excel will FILL IN the sheet
name for you.
 
I've never seen that behavior and I can't get xl2003 to do it now.

You aren't writing about the "refers to" area are you?

I meant the "Names in workbook" area.
 
When I select h1 and goto the name box (left of formula box) and put in joe
without sheet name or quotes the defined name.refers to box =Sheet7!$H$1
When I insert>name>define>type in Bill and enter $h$2 in the refers to box,
I get =Sheet7!$H$2
When I do the macro below I get the same result
Sub doname()
Range("h3").Name = "Sam"
End Sub
 
But the name itself does not include the sheet name--it's a global/workbook
level name unless you do the extra typing (of that sheet name) to make it a
local/sheet level name.

And for the OP, it was important to make the names local/sheet level so that the
names could be used on multiple sheets.

Don said:
When I select h1 and goto the name box (left of formula box) and put in joe
without sheet name or quotes the defined name.refers to box =Sheet7!$H$1
When I insert>name>define>type in Bill and enter $h$2 in the refers to box,
I get =Sheet7!$H$2
When I do the macro below I get the same result
Sub doname()
Range("h3").Name = "Sam"
End Sub
Click to expand...
 
Back
Top