Forum Discussion
Stop recalculating formula past a set date?
I have a summary file that links to each of my project files, respectively. I update the project files weekly. My summary file is set up with column A-C being my project info, Row 2 is my dates, and columns D and beyond are set dates- weekly. (Photo below for reference)
How can I set this summary file up so that:
IF the date in Row 2 on the summary file matches the date on the project file, it will update my numbers.
But if the project file is updated, that summary file column does not update- it keeps its value and the next column with the matching date updates to the current info.
Basically I want to freeze, archive, or deactivate the formula once that date has passed so I can track the numbers across a quarter.
5 Replies
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.
