G
Guest
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
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(CostSheet!$Z11)),(CostSheet!$L11
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,CostSheet!$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?
I have a reasonably long formula as follows that I want to define with a
Name said:=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(CostSheet!$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,CostSheet!$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?