How to stop a formula once a date has passed.

Dakota

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

A Rose

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!😀

Paul Newcome

Are you able to provide some screenshots for reference?

Dakota

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.

Paul Newcome

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?

Dakota

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.

A Rose

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!😀