Google-sheets – Google sheets equivalent of self-referencing in Excel

google sheetsgoogle-sheets-circular-references

This is, in my opinion, one big advantage Excel has over Google sheets is the self-referencing formulas. What I mean by that is: say in cell A1, I'm changing the value in there a,b,c. And according the letters, A2 gives you an output.

Now I have a function in B1 say =if(A1="a", A2, B1) basically acting like a store of value, and if the switch a is pulled, then update the value in B1 with A2, otherwise maintain the current value B1.

This works in Excel but you get a "#REF" in Google. Does anybody have a solution around this?

Best Answer

Please try in A1:

=choose(randbetween(1,3),"a","b","c")

in A2: 1

in B1:

=if(A1="a", A2, B1+A2)

Then in File > Spreadsheet settings... > Calculation turn On Iterative calculation, set Max. number of iterations to 1 and press Ctrl+R a few times. (+A2 as it may show better what is happening.)

Related Topic