IMHO I don’t think there is a canonical answer to this question - there has always been a loud noise from users around certain topics that simply disperse into the eerie silence of the Google documentation development team (this problem seems to be shrouded in the same eternal secret as IMPORTAGE, often failing, or SPLIT without creating the perfect matrix).
I have been bitten by this myself several times, so I have some experience. I used the heath-robinson workaround that worked for me. But YMMV.
The OP question discusses a situation where there is a well-working formula, but after some time, the situation Ctrl + Shift + E has arisen.
(There are other situations where Ctrl + Shift + E may occur due to the results of one formula trying to rewrite to cells written by another formula. In this case, sometimes adding EXPAND at the beginning of the desired formula does the trick. Also, using the Filter functions, and not IF comparisons, does the previous = CONTINUE (1,2,3) cells mop-up the cells ... but maybe none of them will most likely help with the OPs question).
In my experience, a spreadsheet can develop this “confusing” behavior over time, so Ctrl + Shift + E is required, which means a period of time when there are multiple “use” spreadsheets that lead to data changes. I could further suggest that there is a greater likelihood of this confusing behavior when the data mentioned is more dynamic, for example. rows added, rows deleted OR that there are other formulas that are also calculated from the same data set (not to mention the two formulas relate to each other).
I think the confused-behavior idiom is appropriate, since actual spreadsheet development is an open box for Google engineers only; we cannot rationalize his work. So it seems mysterious and magical that in the case of OP and my experience the spreadsheet worked well, but then changed the behavior, requiring Ctrl + Shift + E, without any changes in the formulas, only as a result of changing the accumulated data of the sheets through use.
I gave an example of my workaround using an example of my case. Please note that in my case, the data as well as the problematic formulas are in one sheet, which I call a production sheet
The workaround involves using a script to duplicate the "sheet template" in the same spreadsheet as the production sheet. A template sheet is structurally identical to a production sheet with the same column headings, but with only a few rows of sample data. It has the same formulas as the production sheet, which refers to the sample data in the same template (and not to the sheet). It is important to note that "confused-behavior" is not displayed in the template sheet - Ctrl + Shift + E is not required in the formula cells if there are any changes in the template samples. It also contains the formation of a sheet of products.
So, when the script is run, it creates a duplicate of the sheet template; it subsequently copies the data of the production sheet to this duplicate of the template and reuses the formats. This duplicate becomes a new production sheet. The old production sheet is hidden (in my case, but can be deleted).
I have this system working in a spreadsheet of task tables, which is used by 12 people every day, where Tasks google form during the day (and are copied to the production sheet by another script, not shown as there are no related to this OP). When the task is completed, the task line is removed from the production sheet. Thus, data grows and shrinks continuously throughout the day. A duplicate sheet is created every night using a time trigger in the insertSheet () function. Here is the script I am using:
function insertSheet(){ var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet =ss.getSheets()[0]; //first sheet is the production sheet var sheetRange = sheet.getRange('A3:P'); var sheetValues= sheetRange.getValues(); var d = Utilities.formatDate(new Date(), Session.getTimeZone(), 'ddMMMyyyy-hh:mm:ss'); var ex = sheet.setName('CCEs' + d); var templateSheet = ss.getSheetByName('templateSheet'); var s2 = ss.insertSheet(0,{template: templateSheet}); var height = sheetRange.getHeight(); s2.insertRowsAfter(2, height) s2.setName('CCEs'); templateSheet.hideSheet(); ex.hideSheet(); s2.getRange('A3:P' + (sheetValues.length+2)).setValues(sheetValues); format(); // templateSheet.hideSheet(); ex.hideSheet(); } function format(){ var ss = SpreadsheetApp.getActiveSpreadsheet(); var formatRange = ss.getSheetByName('templateSheet').getRange('B2:N2').copyTo(ss.getSheetByName('CCEs').getRange(2,2, ss.getSheetByName('CCEs').getMaxRows() -1,14), {formatOnly:true}); }
Before deploying this strategy, the table would experience “confusing behavior” in a few days.
The sheet contained three formulas that could be confusing. They are not important for the OP, but I am duplicating here to give the reader a sense of the success of this strategy:
=arrayformula(IFERROR(FILTER(if(row(O:O) =1,"Auto Time Stamp ",iferror(1/0)) &O:O&if(row(O:O) =1,"copy",),len(A:A)),"Error")) =Arrayformula(iferror(if(filter(L:L, len(A:A)) - filter(A:A, len(A:A))>0, if( int(filter(L:L, len(A:A)) - filter(A:A, len(A:A))) = 0 , text( text(filter(L:L, len(A:A)), "HH:mm") -text(filter(A:A, len(A:A)), "HH:mm") , "H:mm") , int(filter(L:L, len(A:A)) - filter(A:A, len(A:A))) & "Day(s), " & text( text(filter(L:L, len(A:A)), "HH:mm") -text(filter(A:A, len(A:A)), "HH:mm") , "H:mm") ) ,iferror(1/0)),"Time Taken")) =arrayformula( IFERROR(if((ISBLANK(FILTER(J1:J,LEN(A1:A))) * (FILTER(G1:G,LEN(A1:A)) = "Normal")* (now()-FILTER(A1:A,LEN(A1:A))> OverdueTimings!A1 )),"OVERDUE Normal", if((ISBLANK(FILTER(J1:J,LEN(A1:A))) * (FILTER(G1:G,LEN(A1:A)) = "Urgent")* (now()-FILTER(A1:A,LEN(A1:A))> OverdueTimings!A2 )),"OVERDUE Urgent", if((ISBLANK(FILTER(J1:J,LEN(A1:A))) * (FILTER(G1:G,LEN(A1:A)) = "Very Urgent")* (now()-FILTER(A1:A,LEN(A1:A))> OverdueTimings!A3 )),"OVERDUE V. Urgent", IFERROR(1/0)))),countif(if((ISBLANK(FILTER(J1:J,LEN(A1:A))) * (FILTER(G1:G,LEN(A1:A)) = "Normal")* (now()-FILTER(A1:A,LEN(A1:A))> OverdueTimings!A1 )),"OVERDUE Normal", if((ISBLANK(FILTER(J1:J,LEN(A1:A))) * (FILTER(G1:G,LEN(A1:A)) = "Urgent")* (now()-FILTER(A1:A,LEN(A1:A))> OverdueTimings!A2 )),"OVERDUE Urgent", if((ISBLANK(FILTER(J1:J,LEN(A1:A))) * (FILTER(G1:G,LEN(A1:A)) = "Very Urgent")* (now()-FILTER(A1:A,LEN(A1:A))> OverdueTimings!A3 )),"OVERDUE V. Urgent", iferror(1/0)))),"OVERDUE *")& " OVERDUE"))
In a few days, we will delete all accumulated old production sheets.