I have the following formula in a sheet:
=if(E1="HOLD",,query(Cust_Orders!B6:Z5000,"Select Y,G,I,H,K where H>0 "))
With E1 being a drop down with values HOLD and FETCH
The recalculation when I go to FETCH takes about 13 seconds.
But when it is in HOLD, the query doesn't exceute, and everything goes blank.
Not what I want.
I'm trying to avoid recalculating this query every time I make a change in the Cust_Orders range, but keep the old values, as two different pivot tables that are viewed by other people are dependent on it.
Google spreadsheet recalc settings all are 'on change and foo'
Is there a way to do this?
Best Answer
There is no built-in way to have a "recalculation on demand" for built-in functions. You could save a copy of the formula somewhere the apply it to the cell when you want it to be calculated, then to "freeze" it, copy and paste the values only. You could use Google Apps Script to automate this.
By the other hand, custom functions are recalculated on open and when one of its arguments changes.
NOTE: On the follow-up question on Stack Overflow, In google sheets can I wrap a standard function in a custom function to control when it is run? I shared the core code lines to implement the suggestion made here. After that I published Spreadsheet Freezer, a Google Sheets Add-on that initially is unlisted as it's no extensively tested yet. The link points a site that describes the add-on and includes a Feedback form to ask for the link.
Related questions