N
nomoney
Hi, I have an extremely large (24MB, 88 interconnecting worksheets) and
complicated Excel Spreadheet (multiple & nested If,l ookup, dget etc
formula).
I use this to automatically update graghs for the past 12 months on key data
metrics (updated monthly).
I noticed mid-last year that when one of my worksheets (let's call it '2006
data') contained more than 8 months of data, the charts did not update as I
switched between input selection list. At this point 'Calculate' shows in
the bottom LH corner in addtion to 'Ready'. However, after searching Help
etc I realised, it seemed, that I had exceeded the number of formula (64K)
for one worksheet. I then effectively split this worksheet into 2 6-month
versions (let's call them '2006_1H data' & '2006_2H data') & all worked fine.
Now, I need to add 2007 worksheets, so I copied '2006_1H data' & '2006_2H
data' into '2007_1H data' & '2007_2H data', plus ,many other 2006 sheets into
2007 versions. After doing this, the 'Calculate' problelm has re-occurred,
even though each individual sheet does not exceed 64K formulae, as per 2006
sheets. If I delete either '2007_1H data' OR '2007_2H data' then no
'Calculate' error occurs & the charts update immediately, so the error is not
particular to one worksheet, but Excel.
My questions are:
1) Is there an limitation to the overall number of formula in Excel2002?
2) Any workaround if overall formulae limitation, other than splitting into
2 files which is VERY messy & will takes amny days?
3) Any other reasons why I get the error & how to fix?
Any input much appreciated!
complicated Excel Spreadheet (multiple & nested If,l ookup, dget etc
formula).
I use this to automatically update graghs for the past 12 months on key data
metrics (updated monthly).
I noticed mid-last year that when one of my worksheets (let's call it '2006
data') contained more than 8 months of data, the charts did not update as I
switched between input selection list. At this point 'Calculate' shows in
the bottom LH corner in addtion to 'Ready'. However, after searching Help
etc I realised, it seemed, that I had exceeded the number of formula (64K)
for one worksheet. I then effectively split this worksheet into 2 6-month
versions (let's call them '2006_1H data' & '2006_2H data') & all worked fine.
Now, I need to add 2007 worksheets, so I copied '2006_1H data' & '2006_2H
data' into '2007_1H data' & '2007_2H data', plus ,many other 2006 sheets into
2007 versions. After doing this, the 'Calculate' problelm has re-occurred,
even though each individual sheet does not exceed 64K formulae, as per 2006
sheets. If I delete either '2007_1H data' OR '2007_2H data' then no
'Calculate' error occurs & the charts update immediately, so the error is not
particular to one worksheet, but Excel.
My questions are:
1) Is there an limitation to the overall number of formula in Excel2002?
2) Any workaround if overall formulae limitation, other than splitting into
2 files which is VERY messy & will takes amny days?
3) Any other reasons why I get the error & how to fix?
Any input much appreciated!