Stop recalculating formula past a set date?

Copper Contributor

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.

allimo33_0-1659720986011.png

 

 

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.

 

@dscheikey 

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.

allimo33_0-1659978476682.png

 

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.

@dscheikey 

Now that removes the zero-

allimo33_0-1659997286925.png

 

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!

@allimo33 

 

 

=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.