Excel2002 - need to close/re-open to update chart ; too many formu

Excel2002 - need to close/re-open to update chart ; too many formu

Post by bm9tb25le » Sat, 27 Jan 2007 03:21:02


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!
 
 
 

1. Need to close and re-open a process pipeline

2. Cannot re-open file to do further update after closing it

I faced the problem that I cannot open the file that I
have just created or modified it even though I have
closed and exit from it. When I closed it and then would
like to open for update again the error message appeared
as follows :
'.... my filename ... is locked for editing by another
user. Do you want to ..Open a read-only copy , ....'
or sometimes the error message appeared :
'.......Close any programs that might be using the file
and try again.'
More info. is that the file is located on our server and
I made sure that I have already closed it and nobody else
open this file.
This problems are sometimes occured sometime not. How can
I solve this problem ?
Your advise will be so much appreciated.
Thanks

3. X glitches when I close and re-open my laptop lid (Update)

4. Error Opening Up form Unit I close DB and re-open

5. Macro to close workbook and re-open new copy

6. Media Player on Vista Ultimate once closed will not re-open!

7. After Closing a Personal Folder, How do you Re-Open it?

8. Journal entry timer still running after Save/Close & Re-open

9. IE6 fails to re-open a second time after closing

10. Unable to re-open a closed opportunity

11. [PATCH][SELINUX] Re-open descriptors closed on exec by SELinux to /dev/null

12. [PATCH][SELINUX] Re-open descriptors closed on exec by SELinux to /dev/null

13. WMP 11 delay close and re-open

14. Cannot re-open files after closing them

15. Remember Cursor Position from Doc close to re-open