Insert\Name\Apply.... problem

[David]

Greetings and TIA for your time
I am trying to unravel a spagetti application that has been hended to me.
Range naming is not a feature of this spreadsheet. I want to apply names
retrospectivly.
I have no problem where the name to be applied is on the same sheet.
Insert\Name\Apply... does not work for me if the name(s) to be applied are on
different sheets to the cell formula I am trying to apply names to.
(I am running Excel 20002 on windows XP)

 

[Frank Kabel]

Hi
just select the range on the other sheet and define a global name?.
See:
http://www.xldynamic.com/source/xld.Names.html

 

[David]

Frank,
Thanks for your response.
The Names i am trying to apply to formulas from other Worksheets ARE global
names.
Here is an example
1) In sheet1 cell A1 I type: = Sheet2!A1 * Sheet2!A2
2) Activate Sheet2 and using the name box, name A1 & A2 "cat" & "dog" -
these are now workbook level names (can be referenced from other sheets w/o
adding "SheetName!")
3) Go back to Sheet1, select A1 then: Insert\Name\Apply... to get the apply
names dialogue box.
4) Select both "cat" & "dog" from the list and press the OK button - results
in message box "Microsoft Excel cannot find any references to replace"

 

[Frank Kabel]

Hi
try entering the following directly as a formula
=cat*dog

 

[David]

Frank,
Thanks again.
=cat*dog would work OK, but this does not throw any light on the problem i
am trying to describe...
The nature of my problem is about applying names to references in formulas
retrospectively. I have been handed a workbook full of formulas and not a
name in sight. I need to make this spaghetti like mess into something that is
readable. Liberal use of range names would be a good start. I want to name
ranges and then update formulas that refer to those ranges such that the
formulas now refer to names instead of cell addreses. Insert\Name\Apply...
works ok provided the "name" and formula to which it is being applied are on
the same sheet. In the case of my spaghetti workbook I need to make formulas
refer to names that are on other shsets. Any ideas?

 

[JBoulton]

How about using find/replace?

Frank,
Thanks again.
=cat*dog would work OK, but this does not throw any light on the problem i
am trying to describe...
The nature of my problem is about applying names to references in formulas
retrospectively. I have been handed a workbook full of formulas and not a
name in sight. I need to make this spaghetti like mess into something that is
readable. Liberal use of range names would be a good start. I want to name
ranges and then update formulas that refer to those ranges such that the
formulas now refer to names instead of cell addreses. Insert\Name\Apply...
works ok provided the "name" and formula to which it is being applied are on
the same sheet. In the case of my spaghetti workbook I need to make formulas
refer to names that are on other shsets. Any ideas?

 

[Jeff]

Frank,
Thanks again.
=cat*dog would work OK, but this does not throw any light on the problem i
am trying to describe...
The nature of my problem is about applying names to references in formulas
retrospectively. I have been handed a workbook full of formulas and not a
name in sight. I need to make this spaghetti like mess into something that is
readable. Liberal use of range names would be a good start. I want to name
ranges and then update formulas that refer to those ranges such that the
formulas now refer to names instead of cell addreses. Insert\Name\Apply...
works ok provided the "name" and formula to which it is being applied are on
the same sheet. In the case of my spaghetti workbook I need to make formulas
refer to names that are on other shsets. Any ideas?

Hi David,

I know this response is late for your issue but because I embarked on a
search for a solution to the same problem and found no answers (other
than using the Replace function), I thought I would post something that
may be helpful to future users with this problem. I found that when
trying to Apply Names for the first time after the Names are Defined,
all of the new names are automatically selected for Applying to the
selected areas of the worksheet, but, like others, also found that
Applying these Names seems to have no affect on previously defined
formulas. However, after tinkering with this issue some more, I also
found that if I come back and try to Apply Names again, and uncheck all
of the automatically selected new Names, and then manually select them
and Apply again, they DO get Applied on the subesequent attempt. I have
seen this behavior before, and have found similar unanswered posts on
the net dating back to 1998 ... hard to believe that this behavior is
still with us so many years (& versions) down the road (I am using
Office 2003), but there it is ... I think this is a verifiable bug
across all versions from at least 97 forward.

HTH,

Jeff