Aug 05 2022 10:40 AM
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.
Aug 06 2022 06:48 AM
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.
Aug 08 2022 10:15 AM
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.
Aug 08 2022 11:43 AM
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.
Aug 08 2022 04:11 PM
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!
Aug 09 2022 08:30 AM - edited Aug 09 2022 09:19 AM
=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.