CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Lemel
Lemel

Reputation: 1

Using cyclical formulas to reduce lag in google sheets?

At work we have a giant google sheet (right now about 7 thousand rows) where we track documents

Lately this sheet started to slow down considerably (5-10 second delay before formulas are calculated)

We have very few formulas. Main one is this - one of our workers types contract id in the first column (A3) and sheet looks up relevant date in another sheet sheet and adds it to other columns (B3). In my example it only adds company name

Im thinking that main problem is that google sheets re-calculates everything (including already filled rows) every time something changes on the sheet. So my question is this - can i use cyclical formulas to make it so formulas are calculated only once?

Our current formulas are similar to this:

=IF(A3 <> "";INDEX('Tech data'!$B$1:$B$22;(MATCH(A3;'Tech data'!$A$1:$A$22; 0))); "")

Adding link to demonstration sheet to better show formulas

My attempted solution:

I made this formula to try and force Google sheets to calculate it only once:

=IF(A3<>""; IF(B3 <> ""; B3; INDEX('Tech data'!$B$1:$B$22; MATCH(A3; 'Tech data'!$A$1:$A$22; 0)));"")

Basically idea is -

  1. If nothing is entered in Company ID column - then return empty space.
  2. Else check if cell with this formula (B3) is NOT empty - then make b3 = b3
  3. Else if b3 is EMPTY - then use index+match formula to get company name

I set google sheets to allow cyclical formulas and set it to only calculate it once

My idea is that should make already calculated cells "locked" - google will know that this cyclical cell was alread calculated once, so it shouldnt calculate it again

I tried replacing all formulas, but havent noticed big improvement (but this table is very temperamental - sometimes it works flawlessly, sometime it lags)

I already used other simple ways i know to make google sheet work faster - removed all unused empty cells, got rid of almost all conditional formatting, made sure that index+match is only running if A cell is not empty. I heard that looking up data on another sheet can also slow things down, but i havent tried to fix that yet. Might just add all relevant data as an array to some hidden columns and use THAT data for clculations

Upvotes: 0

Views: 223

Answers (1)

Tedinoz
Tedinoz

Reputation: 8094

Try this formula in Cell A2 on Main sheet:

=map(A2:A;lambda(v; IF(v="";;VLOOKUP(v;'Tech data'!A1:B;2;0))))

(configured for using ; as argument separators)

adapted from mapping vlookup for each element of splitted list in google sheets

Note: So far as performance is concerned, reduce the number of rows on "Main Sheet" to a minimum, since the formula calculates values for every cell in the column.

SAMPLE

snapshot

Upvotes: 1

Related Questions