4

Simplified scenario:

Sheet Customer_Orders, has blocks of rows with each row having product code, count ordered, and size. Bunch of other stuff is looked up/calculated on the basis of these three tidbits. By the end of the season this sheet has about 5000 rows.

Sheet Raw_Inventory has start of year in the first 500 rows, and then does a query to Customer_orders. By season end this sheet has about 2000 rows.

Near as I can tell, this query runs every time I change one of the 3 fields in Cust_Orders.

Sheet Inv_Status is a pivot table that runs against Raw_Inventory, and again, I think that every time Raw_Inventory is modified, the pivot table is recalculated. (There are a couple of other pivot tables that use the same data.)

The result is that making a change on Cust_Orders can result in up to 2 minutes while the calculations catch up.

(Hardware: Mac Pro, 24 GB ram, 3.2 GHz, 4 core; Current version of Chrome running under Yosemite)

What I would like to do is one of the following:

  • Lengthen the time between updates.
  • Be able to recalculate sheet Raw_Inventory manually.

A partial workaround:

I've created a new sheet that imports raw_Inventory. This copy is used for the pivot table. ImportRange only runs every 30 minutes.

The next step will replace the query with 1 zillion simple assignment statements. I'm hoping that this will replace querying 3000 lines with querying a single line when I make a change in Cust_Orders.

Rubén
  • 34,714
  • 9
  • 70
  • 166
Sherwood Botsford
  • 1,889
  • 5
  • 20
  • 35

3 Answers3

5

I had a similar problem, I solved it by creating an enabling cell and in that cell I put 0 or 1 and then I used that cell inside the formula. In such a way that:

A B
1 enable formula 0
2 = if(B1=0; 0; complex_formula1) = if(B1=0; 0; complex_formula2)
3 = if(B1=0; 0; complex_formula1) = if(B1=0; 0; complex_formula2)


This way when I need to change the spreadsheet I disable formulas (putting 0 on A1), change spreadsheet and on end I enable formulas (putting 1 on A1).

It's not the best solution, but it worked for me.

SauloAlessandre
  • 705
  • 7
  • 9
  • I'd run into this solution before. The problem is that when it's off, all the calculated cells go blank, which means it's no good as a reference. – Sherwood Botsford Jan 15 '22 at 18:00
  • Yes, you right, it's not the best solution. Unfortunately, google spreadsheet doesn't have option to stop recalculate. I searched every where. – SauloAlessandre Jan 19 '22 at 17:59
3

There is no way to disable automatic recalculation in Google Sheets. One option is to replace the formulas by the values either by using copy/paste as value only or by using a script. The advantage of using a script is that it also could be used to add again the formulas when needed.

Related

Rubén
  • 34,714
  • 9
  • 70
  • 166
0

This is not an answer to my original question -- how to control recalculation, but is a workaround, and ultimately a better solution.

Quick restatement of problem:

CustOrders pulled descriptions of inventory off of RawInv sheet.

RawInv updated from CustOrders. This wasn't quite a circular dependency, as RawInv only updated quantities from CustOrders. But it meant that anytime a change was made in CustOrders, RawInv needed to be recalcuated.

This was made worse by having one query per line creating descriptions.

The solution amounted to refactoring.

  • Another spreadsheet was created, CustSupport.
  • It kept RawInv and Trees -- the latter being the descriptions. It also had the master reference sheet for prices and round off tables. These two tabs are rarely changed, and are copied as needed to sheets that use them.
  • It imported a copy of CustOrders. Since this copy had no dependencies back to to main ordering sheet, I didn't have to wait for it to recalculate.
  • RawInv recalculated from this copy of CustOrders.
  • I did a wholesale replacement of Querys with VLookups. This required some rearrangement of columns.
Sherwood Botsford
  • 1,889
  • 5
  • 20
  • 35