Named Ranges

[John]

Hi there,

I've been trying out Jan Karel Pieterse's Name Manager add-in
(http://www.decisionmodels.com/downloads.htm#namemanager) which seems to
work very nicely, but I have a question: The add-in has a function to
convert a Name from Global to Local and vica-versa and when you convert to
local the name changes from:

"Bob" (RefersTo Sheet1!$E$7)

to

"Sheet1!Bob" (RefersTo Sheet1!$E$7)

That's fine, but the name in the drop down box (to the left of the address
bar) continues to show "Bob". So my question is, is how can you retreive
that name ("Bob") in code as both .Name and .NameLocal return "Sheet1!Bob"?

(The context to this is that I'm converting all names with formulas to cell
references.)

Best regards

John

 

[Ron de Bruin]

Hi John

Contact JK about this

See his website for a mail address

 

[Charles Williams]

The safest way (unless the name is a formula) is to reference the name via
the Worksheet.Names("Bob") collection rather than the Workbook.names()
collection.
It also works if you reference the Name in the Workbooks.name collection but
prefacing it with "sheet1!"

If you have a global name and a local name with the same name all bets are
off and life gets tricky (although it can be done ... I just don't recommend
it, there is a Name Manager filter to spot these)

If the name references a formula you should use Evaluate but bear in mind
that application.evaluate defaults to the active worksheet for unqualified
references.

regards

PS: its not Jan Karel Pieterse's Name Manager: we co-authored it.

Charles Williams
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com

 

[John]

Hello Charles,

Thanks for your reply and apologies for not crediting you correctly. I
misunderstood at first reading.

I think was confused about the LocalName syntax (ie putting the sheet ID in
front) so thanks very much for clearing it up for me.

Best regards

John

 

[John]

Hi Ron,

Thanks for your pointer. As it happens Charles Williams replied to my post,
so I've got it straight from the horse's mouth so to speak (no offense
Charles!).

Thanks to you both.

John