Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Insert\Name\Apply.... problem
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) -- David |
#2
|
|||
|
|||
Hi
just select the range on the other sheet and define a global name?. See: http://www.xldynamic.com/source/xld.Names.html "David" wrote: 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) -- David |
#3
|
|||
|
|||
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" -- David "Frank Kabel" wrote: Hi just select the range on the other sheet and define a global name?. See: http://www.xldynamic.com/source/xld.Names.html |
#4
|
|||
|
|||
Hi
try entering the following directly as a formula =cat*dog "David" wrote: 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" -- David "Frank Kabel" wrote: Hi just select the range on the other sheet and define a global name?. See: http://www.xldynamic.com/source/xld.Names.html |
#5
|
|||
|
|||
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? "Frank Kabel" wrote: Hi try entering the following directly as a formula =cat*dog |
#6
|
|||
|
|||
How about using find/replace?
"David" wrote: 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? "Frank Kabel" wrote: Hi try entering the following directly as a formula =cat*dog |
#7
|
|||
|
|||
David wrote: 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? "Frank Kabel" wrote: Hi try entering the following directly as a formula =cat*dog 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Row Autofit problem Excel 2003 | Excel Discussion (Misc queries) | |||
Baffling formula problem | Excel Discussion (Misc queries) | |||
Problem with date base units for x axis | Charts and Charting in Excel | |||
Paper Tray selection Problem, | Excel Discussion (Misc queries) | |||
File is locked for Editing by user problem | Excel Discussion (Misc queries) |