Length limitation of cell formulas
 

Hi Peter,

Your very long formula works OK for me using excel97, excel2000 and
Excel2002.
Check the truncation when in R1C1 mode: I get 1024

I suspect that the internal limit for functioning formulae is probably
based
on the parsed version of the formula rather than the text version, and
the
parsed form contains numeric references to the sheet name rather than
the
sheet name itself: cant check in the SDK because I have lent my copy to
someone.

But there are limits on the size of formula string (and FormulaArray
string)
that can be entered without using the namechange trick.


Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com

"Peter T" <peter_t@discussions wrote in message
...

Hi Tom,

We appear to get different results. I've doubled checked and the
"long"
formula with long sheet name still works, on manual recalc or if I
change
any of the values on Sheet2 A1:A161. I've even pasted back the code
from
my
post. Also saved reopend a test file. No errors.

Another difference - for me the "long" formula truncates to exactly
1000
after changing the sheet name, not 1023. The original formula length
is
1019
(before renaming the sheet).

The 5849 (typo 5489) characters is the calculated length of the
formula
after changing the sheet name from "z" to the long name.

Does it work / not work for anyone else ?

Regards,
Peter T


"Tom Ogilvy" wrote in message
...

that just caused an error on the first message box. (xl2003). The

formula

was truncated to 1023 characters. what is the significance of 5489.
It
appears the limit is 1024 as stated in help. If you do a recalc, you

will
see the truncated formula isn't functional (it isn't secretly working
as

it

appears you are trying to imply).

--
Regards,
Tom Ogilvy

"Peter T" <peter_t@discussions wrote in message
...

typo:


...length of 5489:

should read
length of 5849

Peter T