0

TL;DR

A cell in one sheet in a Google Sheets workbook refuses to update when it should -- i.e. when relevant changes occur elsewhere in the document -- unless and until a recalculation is forced, for example by making an unimportant change to the cell's contents. In particular, the update does not occur automatically if the change in question is to the name of another sheet that is supposed to exist in the workbook.

DETAILS

In cell C1 (say), of a sheet in a Google Sheets "workbook", I have a string, being the name of another sheet in that same workbook:

C1 = Target Sheet

In C2 (say), I then construct a reference to cell A1 (but any cell will do) in that target sheet (the intention being to use it later in INDIRECT()):

C2 = CONCATENATE("'",C1,"'!A1")

And so now, in C3 (say), I can test for the existence of the target sheet using something like this:

C3 = IF(ISERROR(CELL("address",INDIRECT(C2))),
          CONCATENATE("[ERR] Can't find sheet '",C1,"'"),
          CONCATENATE("[OK] Sheet '",C1,"' is present"))

The problem is, the formula in C3 won't automatically update itself when the "existence status" of the target sheet changes. For example, if I delete the target sheet, C3 does not change from showing the [OK] message to the [ERR] one. Same problem if I create it -- C3 doesn't change from [ERR] to [OK]. Ditto if I change the name of the target.

HOWEVER, the logic in C3 is correct. If I give the cell a "kick", by modifying the formula in some unimportant way -- e.g. add or subtract a space character from the message -- then the update occurs correctly. Similarly, if I make the following ostensibly insignificant change to C3:

C3 = IF(ISNUMBER(NOW()), IF(ISERROR(CELL("address",INDIRECT(C2))),
          CONCATENATE("[ERR] Can't find sheet '",C1,"'"),
          CONCATENATE("[OK] Sheet '",C1,"' is present")))

then I can create the "kick" by making a change anywhere in the sheet -- i.e. it doesn't have to be to C3 itself -- and the value from C3 then updates.

Note that the above is all one specific way of showing the problem, but there are others, and to the extent that I am pretty sure the culprit here is NOT the INDIRECT() aspect. Nor is it anything specific to the CELL() function. I can create a problem without using either of those. I suspect it has something to do with how/when changes in a sheet's name get propagated throughout the "workbook".

Any ideas?

  • can you share a copy of such sheet? – user0 Apr 7 at 18:48

Your Answer

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

Browse other questions tagged or ask your own question.