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?

2

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

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

For all we know OP could be dead, but if anyone is having a similar problem:

I ran into a similar problem. I was trying to make a script that only changes a value every hour. I have an auto refresher that runs every minute. I was trying to make the function return the value of the cell so that it isn't changed.

My solution was to pass up the cells ID using both

ROW(), COLUMN()

I saved them as "row" and "column"

to return the cell's value I returned the following:

SpreadsheetApp.getActiveSheet().getRange(row, column).getDisplayValue()

this is effectively the same as passing up the cells own value.

| improve this answer | |
  • It's worthy to note that nowadays Google Sheets supports circular references. See this answer – Rubén Jun 5 at 20:43
0

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.

| improve this answer | |
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, )

| improve this answer | |

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.