Named Ranges
 

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

Named Ranges
 

Hi John

Contact JK about this

See his website for a mail address

--
Regards Ron de Bruin
http://www.rondebruin.nl


"John" wrote in message ...
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

Named Ranges
 

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" wrote in message
...
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

Named Ranges
 

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


"Charles Williams" wrote in message
...
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" wrote in message
...
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

Named Ranges
 

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

"Ron de Bruin" wrote in message
...
Hi John

Contact JK about this

See his website for a mail address

--
Regards Ron de Bruin
http://www.rondebruin.nl


"John" wrote in message
...
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