Forum Discussion
Stop recalculating formula past a set date?
Hi allimo33,
you can do this with a simple IFS() function in conjunction with TODAY().
R4 = IFS(TODAY() = R$2, ValuefromSummary, TODAY() > R$2, R4, TRUE, "") ValuefromSummary stands for the cell that contains your data for this date.
For this formula to refer to itself, iteration must be switched on in the Excel options.
I hope this helps you.
Here's how I edited your formula. When I enter this, it gives me 0.00
=IFS(TODAY() = R$2, '[4475_Program Status.xlsx]Summary'!$A$47, TODAY()>R$2, R11, TRUE, "")
I am trying to match the date in row 2 to the date in a separate file. Like "If this date in R2 is after the date in '[4475_Program Status.xlsx]CV'!$B$1, then it does not update and stays the same value as it was.
Hi allimo33,
the date in line 2 was never less than today's date. Therefore, the value was never drawn from '[4475_Program Status.xlsx]Summary'!$A$47. Please change the date in line 2 once to a value higher than TODAY(). And then set it back again. Then it should work.
Now that removes the zero-
I have attached Example Files of what I work with. Blue cells on each are the dates that need to match up, pink are the cells where the $ value needs to populate once, then freeze (based on dates?) I added comments to these cells as well explaining what I need..... if it's even possible!
=IF(D$2=[Excel Example_Reference File.xlsx]CV'!$B$1,[Excel Example_Reference File.xlsx]Summary'!$A$47,D$5)=IFS(D$4="reset","",D$2>[Excel Example_Reference File.xlsx]CV'!$B$1,"",D$2=[Excel Example_Reference File.xlsx]CV'!$B$1,[Excel Example_Reference File.xlsx]Summary'!$A$47,TRUE,D5)In the second formula, no old values are taken along when the cell is copied. You can also reset the entry by entering "reset" in line 4 above the formula.