Worksheets Names

[Paul Smith]

Is there a way to define a worksheet level name through the user interface
or does it have to be done through code or copying a sheet containing a
workbook level name?

PWS

 

[keepITcool]

in the name dropdownbox or in Define names

simply precede the name with the
worksheetname and an exclamation mark.

sheet1!Myname

be aware that you must avoid having
the same name at workbook AND worksheet level.

download NameManager addin from www.jkp-ads.com
a must have for developers (and many users



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Paul Smith wrote :

 

[keepITcool]

ps:
for worksheet names with spaces etc you must add apostrophes
'my sheet'!my_name

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


keepITcool wrote :

 

[Bob Phillips]

be aware that you must avoid having
the same name at workbook AND worksheet level.

Why? It can be useful. Of course, it c an be abused, but so can anything.

 

[keepITcool]

I said: avoid... it wasnt a definite nono.

A. it can be VERY confusing for "name" newbies.

B. you cannot access (thru VBA or functions)
a global name IF the same (local) name exists
on the activesheet.

I remember an essay from JK Pieterse on following:

C. calculation initiated from VBA may end up with
unexpected results for "named formulas".
depending on activesheet at time time the calculation
was run.

sorry, cant find the link..


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Bob Phillips wrote :

 

[Bob Phillips]

I said: avoid... it wasnt a definite nono.

.... be aware that you must avoid having the same name at workbook AND
worksheet level...

sounds like a pretty definite nono to me.

A. it can be VERY confusing for "name" newbies.

That is true of many things, which doesn't mean others shouldn't.

B. you cannot access (thru VBA or functions)
a global name IF the same (local) name exists
on the activesheet.

You can, it is more work but it can be done. But why would you want to, that
would defeat the purpose of having a same name local name.

I remember an essay from JK Pieterse on following:

C. calculation initiated from VBA may end up with
unexpected results for "named formulas".
depending on activesheet at time time the calculation
was run.

sorry, cant find the link..

That is a shame, I would like to have read that.

 

[keepITcool]

Bob Phillips wrote :

sounds like a pretty definite nono to me.

..hmm

That is true of many things, which doesn't mean others shouldn't.

OP is a newbie.

You can, it is more work but it can be done. But why would you want
to, that would defeat the purpose of having a same name local name.

this is definitely defeating the purpose, but a nice exercise..

but HOW do you want to delete the global name
(or change it's refersto..)

Sub foo()
Dim wks
Names.Add "MyName", "remove me"
For Each wks In Worksheets
wks.Names.Add "MyName", "obsure the global"
Next

End Sub

answer in ROT13:
V oryvrir nqqvat n oynax furrg vf gur bayl jnl.

 

[Peter T]

Hi Bob,

You can, it is more work but it can be done.

AFAIK the only way would be first to activate another sheet. But I would be
interested if you know of way without doing that.

Regards,
Peter T

 

[keepITcool]

the only other (error prone) alternative
to adding a blank sheet is:

create a copy of the "blocking" name
delete the blocking name
delete the global name
recreate the blocking name
delete the copy..


it's error prone as the refersto may be too long
for the names.add method (complex formulas or multiarea's.)


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Peter T wrote :