Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formulas spontaneously convert to numbers
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 a 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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formulas spontaneously convert to numbers
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...ory_Blues.html for more info. -- JNW "Henry" wrote: 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 a 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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formulas spontaneously convert to numbers
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 "JNW" wrote: 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...ory_Blues.html for more info. -- JNW "Henry" wrote: 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 a 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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formulas spontaneously convert to numbers
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. -- JNW "Henry" wrote: 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 "JNW" wrote: 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...ory_Blues.html for more info. -- JNW "Henry" wrote: 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 a 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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formulas spontaneously convert to numbers
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 "JNW" wrote: 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. -- JNW "Henry" wrote: 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 "JNW" wrote: 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...ory_Blues.html for more info. -- JNW "Henry" wrote: 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 a 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert Text to Numbers | Excel Worksheet Functions | |||
convert negative numbers to positive numbers and vice versa | Excel Worksheet Functions | |||
How to convert a series of numbers | Excel Worksheet Functions | |||
extract numbers, convert to date | Excel Discussion (Misc queries) | |||
How to convert Numbers to text | Excel Worksheet Functions |