Formulas Disapearing

[LuisFernandes22]

Dears, I have done a workbook with about 20 pages, with several names and formulas, most of them "IF" like.
After close and open the file/workbook, I notice several formulas absent, from the cell, or printed in the cell, one of 2 possible results. NAD ADQ. Anyone knows if there are a limit in number of formulas, in a cell, or even in a workbook? If anyone could help, I can upload the workbook, just to be analyzed, and eventually tell me what am I doing wrongly. Thanks to everyone. Luis

 

[thisoldman]

Most of the items you mentioned do not depend on hard limits.

You are limited to 64 nested formulas within a cell. Only 4 billion references to a single cell are permitted.
U.S. English reference: https://support.office.com/en-us/ar...720ac390cd&ui=en-US&rs=en-US&ad=US#BMcharting

Twenty sheets sounds as if this could be, unintentionally, a very large workbook. What is the size of the saved workbook?

 

[LuisFernandes22]

Hi thisoldman, my workbook has 500kb size on disk. I have 139 lines, one formula, and a name, on the first cell on each line. The second cell has a name also. There are several blocks in 2nd and 3rd cells with a formula on top.
The formula on first cell, should replicate the contents of same cell, in previous sheet. The 2nd cell replies the status of same cells on previous page, and the 3rd cell analyzes the status of 2nds cells on the same page.
If you would help and see the workbook, I can send it for you. Let me know the place.
thanks for your time
luis

 

[thisoldman]

Without seeing the spreadsheet, there's not much I can offer as help. The forum rules discourage private sharing of files. Is it possible to share the workbook, without exposing confidential data, on a site such as Dropbox.com or Box.com?

500K is big, but not excessive. 20 worksheets linked by formulas might make things complicated. Do you have a lot of formatting? Do you use conditional formatting? Have you tried splitting the workbok?

 

[LuisFernandes22]

Hi thisoldman, I usually do not use, or know how to use a similar place, but I will learn and let you know if I can upload the file to such a place.
The workbook has no private data. It is only made with several lines of text, just showing the levels that kids should acquire, in school, just from 3 to 5 years.
One of my friends, asked me to create this workbook to record and evaluate the growth of his children. He his a child professor, in the ages 3 to 5 years.
3 times a year, he does an evaluation of each kid, and register x in a cell, just if the kid has acquired that status, otherwise, the cell remains empty.
There are 12 sheets to do this, more 6 sheets to show relations between two of such pages, let´s say (sum3&4), and a final evaluation page that should show the status of the 139 items to be evaluated, and in what date, such item was acquired.
I will keep you informed about my progress on learning the upload process.
thanks for your time and interest
luis

 

[LuisFernandes22]

Hi thisoldman
Here his a link to the workbook, I hope it works, since this is the first time I use it.
https://www.dropbox.com/s/gihgzcedibur934/fichadiagavaldigitalalunoV2e.xlsx?dl=0
I have upload one of my workbooks with several formulas missing.
thanks again for your time
luis

 

[MARK858]

Suggestion: You should remove the passwords in the file if you want anyone to assist (I could remove them for myself but don't want to).

 

[LuisFernandes22]

Hi Mark858
Sorry for the forget. I usually need to protect the sheet to test it, just to avoid write over formulas. As I told from the beginning, the workbook should be used protected, to write only on some cells. I have removed all protections, and saved the file again.
thanks
luis

 

[thisoldman]

Calculation is set to automatic.
Approximately six thousand in-cell formulas. Nearly all of them IF() formulas.
3,667 named formulas. The named formulas each name a single cell range on another sheet. No functions are used in the named formulas, that I have seen.
There are approximately 15 merged ranges per sheet. Unmerged, these 15 ranges cover approximately 600–1200 cells per sheet.
Modest use of color fills, font sizes, bold fonts, and borders. No conditional formatting that I've found.

It is obvious a lot of time and effort went into creating this workbook.

Something is corrupting the file, I can repeat the loss of in-cell formulas from copy to copy of the file. I've only seen this in overly complicated workbooks. You can try one or more of the steps below to see if it eliminate the corruption. Unfortunately, these steps will undo much of your previous work. The goal is to reduce unnecessary processing and calculations performed by Excel.

I'm almost sure Excel does a total workbook recalculation, including rebuilding the decision tree, on both Save and on Open when calculation is set to automatic. What I can suggest:


IF() formulas complicate the calculation decision tree. Most of the IF() formulas in the workbook are simple; here's an example:
=IF(ad1fl1<>"", FL1ITEM1,"")

You can replace the IF() formula above with a simpler-for-Excel-to-calculate formula:
=REPT(FL1ITEM1, ad1fl1<>"")

Or, it might be possible to simply remove the function call and simply write:
=FL1ITEM1


Merged cells have frequently given me problems. Not just with formula writing but I've seen Excel freeze from having too many merged cells. I don't merge cells. You rarely see merged cells in workbooks authored by the professionals and the power users of Excel. Do what you can to eliminate them.


Over 3,000 single-cell named references! I don't want to appear mean-spirited, but you extended a good practice too far. It creates a lot of extra calculations for very little benefit. Have you really made the formulas where the names are used easier to read and understand?

 

[LuisFernandes22]

Hi thisoldmen, thanks.
You have pointed the problem I was suspicious about. Excess of calculations. The problem with formulas is that, I only should allow some cells to be filled, if some occurrence exists. For instance, if some child has gettered the level we are evaluating, (in one of 12 pages, XXaval), the next page should not print the text, showing the line/cell empty. I am evaluating with the formula, (IF (previous sheet line/cell is empty, live the cell of this sheet empty, otherwise, fill in the text from the first sheet(same line/cell). I do not know if your proposal works, just like mine, but I should try. In the SUM(soma) page, the line text value should be printed only if the evaluation of such item, was done. The final evaluation page should react too, with some formula, (that sometimes disappear too). It is a pity that the EXCEL cannot deal with such all formating/processing. I know either that people on MrExcell are usually dealing with complex and complicated formulas. My owns, on this workbook are simple, but in big quantity. I do not know how to overcome this problem, but I will do some tests to find another approach. Thanks
luis