Formulas spontaneously convert to numbers

[Guest]

I have a workbook in Excel 2002 that is linked to my ACCPAC (accounting)
database using an ODBC driver called F9. The workbook has 29 worksheets
which access the database to produce a set of financial statements. For some
reason, formulas will convert to numbers without warning. It's different
formulas each month. I don't know if they change when I recalculate
(calculation is set to manual) or when I save the workbook, or when I open
it, but it happens on a regular basis.

I've tried locking the cells, but then the ODBC driver doesn't work properly.

My questions are:

1) Is there a way to fix this problem?

2) Is there an easy way to search through the entire workbook and find the
formulas that have converted to numbers? Currently I have to do this
manually and it can take a very long time to search through all the cells of
29 worksheets each month!

 

[Guest]

There are memory issues with Excel that limity the number of formulas a
workbook can contain and other items. This may be your problem since you
have 29 worksheets and presumably a large number of formulas on each sheet.

see http://exceltips.vitalnews.com/Pages/T1047_Big_File_Memory_Blues.html
for more info.

 

[Guest]

Thanks for that tip and that was a good guess. I checked and despite all the
worksheets, the file comes in at only 1.45 MB. According to the article you
quoted, Excel 2002 has a memory limit of 64 MB, so this shouldn't be an issue.

Do you have a suggestion for my second question (how to search for formulas
that have been converted to numbers?)?

Henry

 

[Guest]

Memory size and file size are different beasts altogether. I couldn't find
the websites that mentioned this. Memory size can be much larger than file
size. So what you are saying below still could be the problem.

As far as finding formulas that have been converted to numbers...
If you know where all the formulas should be, you can check is by pressing
'Ctrl + ~'. This toggles a view in excel between viewing values and
formulas. This is the only way I can think of checking.

 

[Guest]

JNW,

That's beautiful! I knew there had to be an easy way to toggle the view. I
found a number of errors using your answer.

Now to find a solution to the initial problem so that I don't have to keep
fixing the spreadsheet.

Thanks!

Henry