Leaving column cells unchanged in formula
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?
Best 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.
Answers
-
Are you able to provide some screenshots for context?
-
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.
-
And how exactly are you populating the [Change Urgency To] column?
-
This is also manual (along with Urgency Change Date) based on user input, either through grid or through the intake form).
-
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?
-
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.
-
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.
-
Got it, thank you Paul.👍
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.2K Get Help
- 360 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!