Leaving column cells unchanged in formula

Sean Mc
Sean Mc
edited 02/28/24 in Formulas and Functions

Hi everyone,

I need someone far more clever than I am to give me a pointer here.

I have a case in which I need to change a Symbol column's value (which I'll call the ORIGIN column for clarity) if a date in another column (the CHANGE DATE column) is reached.

1) I have the CHANGE DATE and CHANGE-TO VALUE columns. The CHANGE-TO VALUE column is text based on the values here -> https://help.smartsheet.com/articles/2480316-available-symbols-in-symbols-column. Setting the ORIGIN value with something like `=['CHANGE-TO VALUE'@row]` works just fine (but isn't what I need).

2) I have another column (the UTILITY column) which is a formula that says `=IF([CHANGE DATE]@row <= TODAY(), ORIGIN@row, [CHANGE-TO VALUE]@row)` ... basically, "hold the ORIGIN value until the CHANGE DATE is reached, and after that hold the CHANGE-TO value". That also works fine.

What I can't figure out is how to then automatically set the ORIGIN.

I can't use Automation because Automation won't change Symbol columns. And I can't use a Formula because it's a circular reference if I put it in the ORIGIN.


Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Your exact needs are not going to be possible without the help of a third party app such as Zapier, the API, or the premium add-on Bridge. There is a round-about type of way to allow the ability of a manual override, but it still requires the extra column, requires an extra sheet, and can cause scaling issues depending on how often users need to change the initial [Urgency] field.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!