Applying defined names to existing formulas

  • Thread starter Thread starter nippysweetie
  • Start date Start date
N

nippysweetie

tried to use the ‘name’ feature of excel – for named ranges to appear
in formulas instead of cell references?

Eg =licence_costs to appear as the formula instead of =C21

I have selected the source cell C21 in Sheet1 and created a name
licence_cost. I can see the name defined in the menu option Insert/
Name. Refers to =Sheet1!$C$21 (I note an absolute reference is
created automatically)

Attempt1:
=======
however when I move to Sheet2 and select all the cells with formulas
and try to apply my named ranges to existing formulas. by using menu
option Insert/name/apply - I receive the error message

"microsoft office excel cannot find any references to replace"

I can see =Sheet1!C21 is my original formula in Sheet2

Attempt2:
=======
so I try the same as Attempt1 but with deselection of the tick box
under menu option Insert/name/apply/Ignore relative/Absolute => but
with no success. receive same error msg
"microsoft office excel cannot find any references to replace"

Attempt3:
=======
I try editing the formula to read =Sheet1!$C$21 and repeat Attempt1.
=> but with no success. receive same error msg
"microsoft office excel cannot find any references to replace"


I have searched this forum and others for a solution to no avail.
Search/Replace is not really an option - as the scenario above is just
one example of many I would like to use.

Any insight to this infuriating problem much appreciated!
 
Are your named ranges WB specific or WS specific?

Meaning ... is "licence_costs" referencing C21 *only* on Sheet1,
even if it's used on Sheet2,
OR
does it refer to C21 on each of the sheets it's being used in?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
tried to use the ‘name’ feature of excel – for named ranges to appear
in formulas instead of cell references?

Eg =licence_costs to appear as the formula instead of =C21

I have selected the source cell C21 in Sheet1 and created a name
licence_cost. I can see the name defined in the menu option Insert/
Name. Refers to =Sheet1!$C$21 (I note an absolute reference is
created automatically)

Attempt1:
=======
however when I move to Sheet2 and select all the cells with formulas
and try to apply my named ranges to existing formulas. by using menu
option Insert/name/apply - I receive the error message

"microsoft office excel cannot find any references to replace"

I can see =Sheet1!C21 is my original formula in Sheet2

Attempt2:
=======
so I try the same as Attempt1 but with deselection of the tick box
under menu option Insert/name/apply/Ignore relative/Absolute => but
with no success. receive same error msg
"microsoft office excel cannot find any references to replace"

Attempt3:
=======
I try editing the formula to read =Sheet1!$C$21 and repeat Attempt1.
=> but with no success. receive same error msg
"microsoft office excel cannot find any references to replace"


I have searched this forum and others for a solution to no avail.
Search/Replace is not really an option - as the scenario above is just
one example of many I would like to use.

Any insight to this infuriating problem much appreciated!
 
Hi,

If the reference your formulas are relative and your range name is absolute,
the Apply Names command will fail.

One solution is to use Edit, Replace and replace =Sheet1!C21
with Liscence_Cost. Replace All.

if this helps then please click the Yes button.
 
Back
Top