I need a formula in cell M2 to update that same cell ONLY if the current value is NO. Right now, there are 2 possible values for M2 - YES and NO. If the value is YES, I don't want the formula to execute. The current formula in M2 is the following: =IFNA(VLOOKUP(N2,Client_Tested[[Lookup]:[Tested]],2,FALSE),"NO") The problem is that the values in the table Client_Tested only back 35 days. If something was tested 40 days ago (as an example), this formula would mark that row as being not tested, when that's not true. I have played around with the following formula: =IF(M2="YES","YES",IFNA(VLOOKUP(N2,Client_Tested[[Lookup]:[Tested]],2,FALSE),"NO")) This formula does not like that I'm running a formula against the same cell with the formula. Can you help? Ideally, I would like not to create a new column.
This is a circular referencing issue. I do not think there is a solution for this as this value is coming from the cell itself. You can write a formula to evaluate the "YES" situation in the cell however. Like IF(some condition;"YES";another condition)
İf the user would select the "YES" option than this is not possible due the cell content will also be changed. When the user changes the selection the old contents will be overwritten.
You can try to check the "Enable iterative calculation" check box in File-->Options-->Formula however even this can not do much as the cell value is dependent to user selection.