Define Name Truncates Formula?
 

When I attempt to define a name for a long formula, Excel seems to truncate it.

I have a reasonably long formula as follows that I want to define with a
name (i.e. Insert <pulldown menu, Name, Define):

=(($AF11*EME_RT)+($AG11*EPL_RT)+($AH11*ESE_RT)+($A I11*GTW_RT))*(IF(AND((ISBLANK($AA11)),(ISNUMBER($Z 11)),($L11
=2)),((1+($L11-1)*$Z11)^(-1)),1))*(1+ISNUMBER($X11)*$X11)*(IF(OR($AA11="NR", $AA11="R"),1,$L11))

When I copy the this formula into "Refers to" in the Define Name dialog box,
it looks completely correct, then I hit the Add button (still OK), then OK
button and get "The formula you typed contains an error". Except, the
formula works fine in a cell and when I copy the formua back from the dialog
box, I get a truncated/modified version of it as follows:

=((CostSheet!$AF11*EME_RT)+(CostSheet!$AG11*EPL_RT )+(CostSheet!$AH11*ESE_RT)+(CostSheet!$AI11*GTW_RT ))*(IF(AND((ISBLANK(CostSheet!$AA11)),(ISNUMBER(Co stSheet!$Z11)),(CostSheet!$L11
=2)),((1+(CostSheet!$L11-1)*CostSheet!$Z11)^(-1)),1)) 1

I have temporarily gotten around this problem by breaking the SAME forumula
into two parts, then multiplying them together in the cell - it works.

Here's a related problem, some named formulas WORK PROPERLY but when I copy
them back from the Define Name dialog box, they are mangled (funky characters
and again, truncated). The problem here is that I now have no reference to
the actual formula and therefore, no way to modify (or know) the formula!
Below is an example:

=CostSheet!$AE11/(IF(ISBLANK(CostSheet!$U11),GEN_MRK,CostSheet!$U11 ))*(1+IF(ISBLANK(CostSheet!$V11),MAT_ALL,CostSheet !$V11))*(1+IF(ISBLANK(CostSheet!$W11),WAR_ALL,Cost Sheet!$W11))
CostSheet!$AD11 ISBLANK(CostSheet!$T11)΀SP_MRK CostSheet!

Question 1: Is this an Excel bug or limitation when named formulas are too
large?

Question 2: Is there ANY way I can retrieve (copy) the actual formula?

Define Name Truncates Formula?
 

Anyone?!?

"Jon L" wrote:

When I attempt to define a name for a long formula, Excel seems to truncate it.

I have a reasonably long formula as follows that I want to define with a
name (i.e. Insert <pulldown menu, Name, Define):

=(($AF11*EME_RT)+($AG11*EPL_RT)+($AH11*ESE_RT)+($A I11*GTW_RT))*(IF(AND((ISBLANK($AA11)),(ISNUMBER($Z 11)),($L11
=2)),((1+($L11-1)*$Z11)^(-1)),1))*(1+ISNUMBER($X11)*$X11)*(IF(OR($AA11="NR", $AA11="R"),1,$L11))

When I copy the this formula into "Refers to" in the Define Name dialog box,
it looks completely correct, then I hit the Add button (still OK), then OK
button and get "The formula you typed contains an error". Except, the
formula works fine in a cell and when I copy the formua back from the dialog
box, I get a truncated/modified version of it as follows:

=((CostSheet!$AF11*EME_RT)+(CostSheet!$AG11*EPL_RT )+(CostSheet!$AH11*ESE_RT)+(CostSheet!$AI11*GTW_RT ))*(IF(AND((ISBLANK(CostSheet!$AA11)),(ISNUMBER(Co stSheet!$Z11)),(CostSheet!$L11
=2)),((1+(CostSheet!$L11-1)*CostSheet!$Z11)^(-1)),1)) 1

I have temporarily gotten around this problem by breaking the SAME forumula
into two parts, then multiplying them together in the cell - it works.

Here's a related problem, some named formulas WORK PROPERLY but when I copy
them back from the Define Name dialog box, they are mangled (funky characters
and again, truncated). The problem here is that I now have no reference to
the actual formula and therefore, no way to modify (or know) the formula!
Below is an example:

=CostSheet!$AE11/(IF(ISBLANK(CostSheet!$U11),GEN_MRK,CostSheet!$U11 ))*(1+IF(ISBLANK(CostSheet!$V11),MAT_ALL,CostSheet !$V11))*(1+IF(ISBLANK(CostSheet!$W11),WAR_ALL,Cost Sheet!$W11))
CostSheet!$AD11 ISBLANK(CostSheet!$T11)΀SP_MRK CostSheet!

Question 1: Is this an Excel bug or limitation when named formulas are too
large?

Question 2: Is there ANY way I can retrieve (copy) the actual formula?