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

 

[LuisFernandes22]

Hi thisoldmen, formula proposed, REPT, is used to repeat several times the text. This is not the function needed. Formula IF is needed to decide, if the present cell should be filled or not. This not a simple repeat of previous cell contents.
Thinking about excess of processing, You mentioned that excell does a calculation any time it saves or loads a workbook, so, I have done a choice now, and just will test it in next few days, that is to remove the auto calculate choice, and forcing it to be done only manually, as far as I have the sheet filled completely. I will test in next few days, (change the filling, force calculation, and then save it again), just with calculation blocked, (manual).
I will keep you informed of the final results.
thanks luis

 

[thisoldman]

You did not try the formula, did you? It works.

It works because Excel will coerce TRUE to the integer 1 and FALSE to 0.

Book1
	A	B	C	D
1
2		String to Repeat	thisoldman
3
4		Value	IF()	REPT()
5		1	thisoldman	thisoldman
6		2
Sheet1

Cell Formulas
Range	Formula
C5	=IF(B5 = 1, $C$2, "")
C6	=IF(B6 = 1, $C$2, "")
D5	=REPT($C$2, B5 = 1)
D6	=REPT($C$2, B6 = 1)

 

[LuisFernandes22]

Hi thisoldman, I have used copy paste to put the formula on one cell, and got an error. I have not deeply tried it. Looking into formulas explanation, there is a text saying that the formula is used to repeat some text, X number of times. This on excel2007 of course. I am using excel 2007, do you think it works on 2007? Since you show this example, I will try it again for sure.
Thanks once more
luis

 

[thisoldman]

I'm sorry, I got the logic backwards. I do apologize.

On sheet 2, 2ªaval:
E5: =REPT(fl1item1, ad1fl1 = "")
E6: =REPT(fl1item2, ad2fl1 = "")


On sheet 3, soma1&2:
E4: =REPT(fl1item1, ad1fl1 <> "")
F4: =REPT(fl2item1, ad1fl2 <> "")

 

[LuisFernandes22]

Hi thisoldmen, I was just testing the formulas proposed, and they are working. The problem is that, (I am now using WPS over Win10), the ", comma" does not work as a separator. I was forced to replace them with "; double commas). I think that in Excel is the same, as i use ; normally as a separator, and when I have pasted your formula, on Excel, it blamed that the formula was incorrect.
Now, I would like to ask you, replacing all formulas, similar with the solution proposed by you, we will reduce the processing over the system, lightening it?
thanks again
luis

 

[thisoldman]

You will reduce it some by eliminating the IF() functions, but I don't know if it will be enough.

Your workbook is very difficult to test: I cannot add or delete or even easily copy worksheets. All the named absolute-reference single-cell ranges make it so I cannot create one formula and copy and paste it into other cells. Then add to that my unfamiliarity with Portuguese.

You have made some decisions that are awkward. The ideal workbook would not contain what are essentially empty worksheets—worksheets would be created on demand from a template.

Six thousand cascading formulas to toggle text display creates difficulties. Can you not hard-code some of the labels and category items?

 

[LuisFernandes22]

Hi thisoldman, I have done a compilation of several screenshots, related with example pages and their typical formulas. You can see it in:
https://www.dropbox.com/s/domjw955mn74h3q/Form&ScreenShots.docx?dl=0
I have used some of your proposed formulas, and in next time, I will replace the other ones. I hope you can understand better the behavior of the workbook.
I have tried to use formulas linking the source text from the first sheet, (instead of previous page), to insert on other ones, upon the formula should print it, but the new formula will be more complex and bigger.
I should always analyze two cells, to decide if the result is true or false. Wit original formula, I simple replace the page/line with the text on previous page. If I decide to use always the text in 1st sheet, I must analyze two cells on the first page, each line. So the formula became worse, in my point of view.
Thanks again
luis

 

[thisoldman]

On sheets named like Nªaval, could you not just have the hard-coded text in column E? I assume what is truly significant to you and your users is if there are entries in columns F and G. It's hard to give up the magic of text appearing in what was previously a blank cell, but you would eliminate thousands of formulas.


Here's another possible formula replacement. On Sheet 1, cell F4, you have:

=IF(AND(ad1fl1<>"",ad2fl1<>"",ad3fl1<>"",ad4fl1<>"",ad5fl1<>"",ad6fl1<>"",ad7fl1<>"",ad8fl1<>"",ad9fl1<>""),"ADQ","NAD")

This next formula accomplishes the same thing and is much easier for me to copy down to similar cells on the same sheet or copy to other sheets and then modify the tested ranges:

=IF(COUNTA(F5:F13) = ROWS(F5:F13), "ADQ", "NAD")

 

[LuisFernandes22]

Hi thisoldmen, you are using a lot of time trying to help, thanks for that. The formula you pointed, is analyzing the block of the previous page, saying that the block was validated in previous page. I think the final user my friend, is asking to remove that formula, (just the left formula in the header of each block/color). There will be remaining the formula on right side, the big one, with several IFs, and several ANDs. Trying to explain better, all X/aval is related with one time in the year, where kids are evaluated, lets say, in the beginning of the year, after 1st period, christmas, after 2nd period Easter, and in the end of the year. In summer. That sums 4 evaluations, with 4 sheets filled in some cells. Lets imagine that one kid, has the capacity to acquire all the items on a block, yellow block for example. All cells of that block purple column will be filled with X. In the next sheet, person filling the sheet, in next evaluation knows that all items of that block are absent, Lines for item names are clear. Analyzing the previous page, I write in the top of the column, left side, ADQ, which means that all/block, was acquired in previous evaluation, previous sheet/page. So far so good.The formula on top column on the right side, is evaluating the status of both cells on the left part of the line, on sheet in use. The line itself, where the item name is displayed should be empty, (items evaluated are not propagated along next cells in the workbook). Also, if (on that block) one of left cell has an item name on it, but the corresponding cell in purple color, is filled with an X it means that Item is now evaluated. If all block items are evaluated, some cells in previous sheets, other on the sheet, where we are working on, the top column should say ADQ. It means ADQuirido/acquired.
Soma pages are simple showing text of each item that was filled on the corresponding Aval.
The formula you send me, evaluates the present sheet blocks, and not the previous sheet cell blocks, as I do. Can I pointing your formula to a different page, previous one for example?
many thanks algain
luis

 

[LuisFernandes22]

Hi thisoldman, the formula proposed by you could be used, once we insert the name of the sheet(previous), and the formula will refer values from the previous sheet. I am thinking to remove those formulas, on the column with purple cells. Next time we will meet, we will confirm the remotion. It means remove, at least 120 formulas. Meanwhile, I am replacing all formulas in lines, with the 2 formulas proposed by you. I will return in some days.
thanks luis