Getting a 'formula too long' error when trying to save.

[Shiremaid]

"One or more formulas in this workbook are longer than the allowed limit of 8192 characters. To avoid this limitation save the workbook in the Excel binary Workbook format"

So I have a workbook with about 40 tabs.
I went in and on one tab inserted a copied row (from the same tab). There are some longer formulas in the rows, but even the longest formula is only a little over a hundred characters. I changed my view to see the formulas in the cells instead of the results and don't see any on the tab that don't match what I put in. I didn't do anything to the other tabs.

This has happened to me before and I just closed without saving and remade my change with no problem. However, I would really prefer to prevent the error from occurring at all as I am not the only one using this workbook and sometimes we need to make multiple changes at a time.

Does anyone know how or why this is happening?
This is the beta test of a "database" and I would really like to know if I can prevent this error from coming back in the rebuild to go live with our projects
(Also, I know Excel is not a database program and we have a real one coming but can't wait until it's arrived to have something at least minimally functional)

Thanks

 

[oldbrewer]

make a copy
make a copy of the copy
delete all formulas in tab1
save
if saves ok the problem is not in tab1
continue and test all 40 tabs
say problem in in tab7
start deleting row by row
etc etc
you will home in on the issue

 

[Shiremaid]

Thanks for the suggestion oldbrewer.
However, my problem isn't getting rid of the error after if happens, it's trying to figure out how it's happening in the first place.

 

[oldbrewer]

first step is to find out all you can about the error - where it is - what sort of formula..........

 

[Oeldere]

you get passed the restricted amount of if-formula?

 

[oldbrewer]

if it saves ok after deleting tab1 the problem is in tab 1
if it doesnt, delete tab 2 also - - - and so on....

 

[Shiremaid]

you get passed the restricted amount of if-formula?

- What is the restricted amount of if formula?

I tried to find out where the error had happened, unfortunately I have also been having a problem with the spreadsheet crashing and it did so during the process. And as I said in my original post, the error seems to happen randomly so when I re-opened the document and made the exact same change as I had before, it saved fine with no error.
So I am wondering if there were any known problems that could cause excel to randomly think a formula is too long or to randomly change one so that it is too long.

 

[Oeldere]

with the F1 (help) I found it is 64 if formula.

 

[Shiremaid]

with the F1 (help) I found it is 64 if formula.

Ah, thanks. No, none of my formulas nest more than 3 ifs.

 

[thuhoang]

For me after trying to delete the conditional formatting, running Macro, saving in binary and older Excel version 2003 and prior (having about 1/5 of data capacity than new Excel version 2019) did not work for me, I have to recreate the file by copying and pasting each individual tab to the new file to detect and eliminate the tab with "over 8192 characters in formula", which i believe is some kind of coding error in Excel to misread text as formula. Hope it helps!