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

  • A Rose
    A Rose ✭✭✭✭✭
    edited 08/26/21 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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Are you able to provide some screenshots for reference?

  • Dakota
    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
    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
    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
    A Rose ✭✭✭✭✭
    edited 08/26/21 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!😀

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!