How to stop a formula once a date has passed.
I have this formula:
=IF([Hidden Team:]@row = "ALBQ", {[Rosters] 2021 CC Team Range 1})
The info my formula is pulling is date sensitive, and the info will change over time.
Once a date in the row is reached I do not want the formula to perform anymore. I need to freeze the value that was returned once a date in the row is reached.
Thank you,
D
Best Answer
-
Hi,
You can create a second column that records the information of the first column as follows:
You'd need to create a second sheet, then on the first sheet create a formula when date reached move to second sheet,
on the second sheet create a new column "Column 2 Copy",
Create formula on sheet 2 in this new "Column 2 Copy" eneter: =[Column 1] (Convert to column formula)
Create formula on sheet 2 when row are added, move to sheet 1,
this way the info. will be kept in "Column 2 Copy".
as you can't change a column hiperlink per row, if it is set to "Convert to column".
Did that help?
Thank you!😀
Answers
-
Are you able to provide some screenshots for reference?
-
Providing as much as I can:
Top Photo : {Tracker Sheet} - Once the date on a row is reached, I need the hidden truck number to freeze - and not change if the Team changes Truck numbers later in the year.
Bottom Photo: {Rosters} this is my reference sheet - this sheet has NO Formulas. This is where I pull my truck numbers and make sure dates match.
-
How is the second sheet populated? I see you indicate no formulas, but when a team changes truck numbers do you insert a new row or are you changing the existing row?
-
That is a value that we manually type in - if changes are needed.
That is the same row, we change truck numbers in this sheet
If one truck is in the shop for maintenance or repair, they may drive a different one. This is why we want to make sure once the "Form due date" has passed the truck number does not change.
-
Hi,
You can create a second column that records the information of the first column as follows:
You'd need to create a second sheet, then on the first sheet create a formula when date reached move to second sheet,
on the second sheet create a new column "Column 2 Copy",
Create formula on sheet 2 in this new "Column 2 Copy" eneter: =[Column 1] (Convert to column formula)
Create formula on sheet 2 when row are added, move to sheet 1,
this way the info. will be kept in "Column 2 Copy".
as you can't change a column hiperlink per row, if it is set to "Convert to column".
Did that help?
Thank you!😀
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!