2

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?

1

Unfortunately, unless the programming itself is altered, it is impossible to self-reference in Google spreadsheets. In fact, no matter what else is in the formula, it will ALWAYS result in #REF, even if it's =ISERR(A1), =ISERROR(A1), =ERROR.TYPE(A1), =IFERROR(A1,1), or even =IF(ISERR(A1),1,0).

I know it sucks, it really does, but there's nothing we can do except look for another spreadsheet that does allow self-reference.

1

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.)

  • 1
    The point being, the feature is now available but it needs to be enabled in Spreadsheet Settings. – user135384 Jan 14 '18 at 2:52
0

Cells either contain predefined data or the results of a formula. It can't be both. This is why Google Sheets provides the "circular dependency detected" error.

If you remove the reference to B1, the error goes away and the formula doesn't act on the cell when the If condition is false. =if(A1="a", A2, )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.