How to "Freeze" Values in a Row with Dependency on TODAY()
I have a Sheet 1 that collects forms submitted by Program Developers every 2 week to provide an update on a program development project. One of the form fields is "Program Code".
I have a Sheet 2 that lists all of the programs currently in development. One of the columns (Stage@row) is a dropdown with possible values of 1, 2, or 3. Another column is [Program Code]@row. EXAMPLE: Program Code 5,000 may be at Stage 2 of the development process.
In Sheet 1, I have a cross-reference sheet formula in the column (H-Stage@row) that looks at Sheet 2 for the Program Code and returns the value of the Stage@row column. It Matches the Program Code submitted in the form in Sheet 1 with Program Code in Sheet 2, and returns the Stage.
=IFERROR(INDEX({ProgramDevelopment-Stage}, MATCH([Program Code]@row, {ProgramDevelopment-ProgramCode}, 0)), "")
Since a Program Developer is asked to submit the form in Sheet 1 every two weeks, the Program Code will appear in several rows in Sheet 1, but I want to retain the stage during which the Program Developer submitted the form. EXAMPLE: A program development takes 8 weeks, so there would be at least 4 form submissions, and those submissions have occurred in Stage 1, Stage 2, and/or Stage 3.
PROBLEM: The cross-reference sheet formula updates every time I open Sheet 1, rather than retaining the stage # at the time that form was submitted. So if I looked at every form submission for Program Code 5,000, the Stage is always 3, when actually one form was submitted when the project was in Stage 1, two while in Stage 2, and three while in Stage 3.
My first thought was to compare the "Created Date" with TODAY() and somehow only update the Stage@row in Sheet 1 when they matched (since a Program Developer would only submit the form once in any given day). However, I can't figure out how to set up any other Helper columns that will keep the stage # that was set when previous forms were submitted. If I use if IF(They Match, CROSS-REF FORMULA, "otherwise don't change the Stage")—-but how do I tell it to keep the stage, for example "2" when the form was submitted last month, even though in Sheet 2 it has recently been updated to "3" when the form was submitted today?
HERE'S WHAT IT SHOULD BE (The "2" and "1" under Stage "freeze" rather than updating as it is in Sheet 2.
Created Date | TODAY() | Program Code | Stage
1/30/25 | 1/30/25 | 5,000 | 3
12/22/24 | 1/30/25 | 5,000 | 2 ←-Should not update
11/30/24 | 1/30/25 | 5,000 | 1 ←-Should not update
HERE WHAT IS HAPPENING: The cross-reference sheet formula causes Stage to update because its updating in Sheet 2.
Created Date | TODAY() | Program Code | Stage
1/30/25 | 1/30/25 | 5,000 | 3
12/22/24 | 1/30/25 | 5,000 | 3 ←- Updating cause its updated in Sheet 2
11/30/24 | 1/30/25 | 5,000 | 3 ←- Updating cause its updated in Sheet 2
I eventually want to be able to analyze the responses to the questions in the form by Stage, and I can't do this when the Stage is updating as the Stage is updating in Sheet 2.
Any thoughts?
Answers
-
Ahhh.. yes… this issue… the way I've handled this is to a copy row and clear value automation to "freeze" the info in a data collection sheet - and then run my reports on that sheet. For this I would have an automation that would clear out the values AFTER the Copy Row, then you know it will be ready to take on the next value. I also just collect it in one column - rather than have different ones for the different phases.
You MAY be able to set up a helper column to flag - set the helper with an automation (when rows are changed and Phase1 - 3 are any value —> set condition blocks —> Phase 3 is not blank and Flag is not set, then set the flag, Phase 2 is not blank and Flag 2 is not set —> Set Flag 2, Phase 1 is not blank and Flag 1 is not set —> Set Flag 1
IF(FLAG X <> true, FORMULA) - no need for a false…
Of course you would need a flag for each of the columns.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66K Get Help
- 429 Global Discussions
- 149 Industry Talk
- 488 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 153 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!