I help a small business with an application which is entirely run from google sheets and google forms. The issue is that from time to time the business owner will go into the sheet and delete rows of old data. I've noticed that as a result, some of the formulas will change the row which they are looking at. This is despite me using explicit cell references such as $A$1 for example.

So what I would like to do, is use somehow compare the cell which contains the formula, with a string of what the formula should be, then return a value if the formula matches. I had thought an =If function would solve this but so far I am not having any luck.

This is what I have used so far:

Lets say cell A1 contains the formula I want to check and the formula is (this works fine): =FILTER(Dashboard!A2:A, Dashboard!C2:C1 = TODAY())*

I am using =IF(A1="=FILTER(Dashboard!A2:A, Dashboard!C2:C*1 = TODAY())",True,False)

I am expecting the function to compare the value of A1 with my string and return the value True. If the formula does not match the string it should return the value False.

I currently am returning the value of False despite the formula and string being an exact match. I assume this is because my =If statement is looking at the value returned by the =Filter function rather than the formula itself.

Any help would be appreciated.

thanks

CodePudding user response：

You could just use INDIRECT() in the place of explicit cell ($A$1) reference you mentioned and that should solve the problem of formulas going haywire due to rows deletion in Form Responses tab. Please do test it out.

INDIRECT("Sheet!"&A1)

INDIRECT("A1")