Applying names to an existing formula?

[grovesteyn]

Hi

I have defined a global name in sheet A. I want to apply it to an existing formula in sheet B. I am using excel 2010. I place the cursor on the cell with the formula that needs changing. The normal approach of doing "Define Name", "Apply Names" does not work. I get the "Microsoft Excel cannot find any references to replace" error. However, if I do the same procedure with a formula in the same sheet as where the named cell resides it works.

Am I doing something wrong, or is it not possible to apply global names defined in sheet A to formulas in sheet B?

Grové

 

[pgc01]

Hi Grové
Welcome to the board

I don't think, unfortunately, that that will work. I've already posted (more than 3 years ago) here:

apply named cell from another worksheet to existing formula

in post #4. You can use find/replace but read my note there.


IMO, it's strange that this is not yet possible, at least to apply names that refer to ranges in the same workbook, but it seems that you still can't.

This is a very old problem, see this ms article for excel 2000!

XL2000: Name Cannot Be Applied to Formulas on All Sheets in a Workbook


Remark: I just tested in excel 2010, but I don't have yet excel 2013, so I don't know if this is allowed there.

 

[grovesteyn]

HI PGC

Thanks - you have confirmed my worst fears. This would be particularly useful in situations where one is trying to clean up someone else's overly complex spreadsheet, or attempting to tame one's own beast that has outgrown its original spec.

Grové

 

[pgc01]

You're welcome.

For further help I'd look at the Name Manager tool from Jan Karel Pieterse, Charles Williams and Matthew Henson.
Name Manager

Coming from them I'm sure it will be a great help.