Leaving column cells unchanged in formula

Sean Mc

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.

Help?

Paul Newcome

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.

Paul Newcome

Are you able to provide some screenshots for context?

Sean Mc

Sure! The existing sheet has 20 columns and has some sensitive info, so I mocked up this sheet that has all the relevant columns. The formula for the column labeled "(util) Urgency Text" is `=IF([Urgency Change Date]@row <= TODAY(), [Change Urgency To]@row, Urgency@row)`

If there are other/better ways to go about achieving the needed result ("change Urgency to the Change Urgency To value on/after the Urgency Change Date"), I'm 100% happy to hear any ideas.

Paul Newcome

And how exactly are you populating the [Change Urgency To] column?

Sean Mc

This is also manual (along with Urgency Change Date) based on user input, either through grid or through the intake form).

Paul Newcome

So you are manually inputting the initial [Urgency], the [Urgency Change Date], and the [Change Urgency To] fields, and you want the [(util) Urgency Text] field to automatically select one of the manually input urgencies based on the manually input date?

Sean Mc

No... the [(util) Urgency Text] was just an intermediate attempt at getting where I want to go. The value in that field is what I want in the [Urgency] column. The problem is, automation won't allow me to change a Symbol field.

The use-case version of what I need is:

The user needs to be able to freely set/change [Urgency], [Urgency Change Date], and [Change Urgency To]. On the [Urgency Change Date], the value of [Urgency] needs to be set to the value in [Change Urgency To]. This is not permanent -- the user still needs to be able to change that value if they want to.

I'm very sorry this is unclear! I realise my explanation is inadequate... not sure how to describe it otherwise.

Thanks for taking the time to look.

Paul Newcome

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.

Sean Mc

Got it, thank you Paul.👍