Formulas for

Options

Is there a way to change a formula to a static value, once a date (or other item) is entered.

ie. So once a formula pulls info it updates the cell. At the end of the month, ( a date is reached) it removes the cell formula, but the info in the cell stays in place.

Answers

  • Purnima Gore
    Purnima Gore ✭✭✭✭✭✭
    Options

    Hi

    Could you use another column and use an automation with update cell value?

    PG

    Purnima Gore

    Cierr Limited

    Your Time is Important, you want to Stay on Track, We can help you use the Right Tools

    https://www.cierr.com

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    edited 12/29/22
    Options

    Hi, @Dakota, unfortunately, that is not supported.

    If locking the row is not an option (to prevent changes to columns that the formula uses) then you can leverage "Move Row" as a workaround. When you move a row to a new sheet, the dynamic value becomes static IF the corresponding column on the target sheet does not use a formula.

    In the example below, (#1) the value in "Mileage - Current" depends on a formula and changes regularly. At month end, a trigger moves the row to another sheet (#2) that has the same columns but has only one column formula. In "Mileage - MonthEnd" that formula sets the value to whatever is in "Mileage - Current". After which a workflow automation moves the row back to the source sheet (#3) where the value "Mileage - Current" subsequently updates but the value in "Mileage - MonthEnd" remains static.

    Another approach is to use the automation action, "Copy Row", and copy the row into the second sheet. You then have a cross-sheet formula in the orginating sheet that "grabs" the value from the second sheet. The second sheet has no formulas so the values never change.

    Hope this helps!

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Hi @Dakota

    I hope you're well and safe!

    Please have a look at my post below with a method I developed.

    More info: 

    Would that work/help?

    I hope that helps!

    Have a fantastic week & Happy Holidays!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!