Applying defined names to existing formulas

[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!

 

[RagDyer]

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!

 

[ShaneDevenshire]

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.

 

[ShaneDevenshire]

Hi,

Leave out the = sign in my previous post , Replace what Sheet1!C21