Automatically updating Status column based on presence of data in other cells
I'm trying to have my Status column update automatically based on other columns being empty or not empty. For example:
IF Column A = empty, THEN Status = "New"
IF Column A = not empty, THEN Status = "In Progress"
IF Column B = not empty, THEN Status = "Awaiting Approval"
IF Column C = not empty, THEN Status = "Done"
In my workflow, Columns A-C will each be filled out by a different user, and will always be filled in in that order (A, then B, then C). Therefore, I don't need to worry about IF Column A AND Column B = not empty. I can just know that if data is present in X column, display Y status.
I'm assuming I can do this by using an IF statement that has the logical expressions in the correct order (i.e., check Column C first, then Column B, then Column A).
For some background, I'm working around the fact that SS doesn't allow me to set a value in my Status column based on the submission of a form/ update request/ approval. Also, using a formula isn't ideal, because I would like for users to be able manually update the Status column (if I use the IF formula, and then a user manually overrides it, it will prevent the Status from updating automatically in the future).
Is there a better way I can be doing this? Or if not, does the strategy I laid out make sense/ have any flaws?
Thank you in advance for any and all help!
Best Answer
-
You might need to rework the order in your data but I think this should work
=IF(NOT(ISBLANK([Column B]@row)), "Done", IF(NOT(ISBLANK([Column B]@row)), "Awaiting Approval", IF(NOT(ISBLANK([Column A]@row)), "In Progress", "New")))
If you're using a form though you could add your Status Column into the form as a hidden cell that populates with a default value - I assume that would be "New". But I haven't used this for a field that would subsequently be updated via formula. This approach may require someone to copy/paste the formula for all new rows.
As for avoiding formula overrides - my best practice is to lock down columns that run automations like this so that only people with Admin permissions can override.
Kelly Drake (she/her/hers)
STARBUCKS COFFEE COMPANY| business optimization product manager
Answers
-
You might need to rework the order in your data but I think this should work
=IF(NOT(ISBLANK([Column B]@row)), "Done", IF(NOT(ISBLANK([Column B]@row)), "Awaiting Approval", IF(NOT(ISBLANK([Column A]@row)), "In Progress", "New")))
If you're using a form though you could add your Status Column into the form as a hidden cell that populates with a default value - I assume that would be "New". But I haven't used this for a field that would subsequently be updated via formula. This approach may require someone to copy/paste the formula for all new rows.
As for avoiding formula overrides - my best practice is to lock down columns that run automations like this so that only people with Admin permissions can override.
Kelly Drake (she/her/hers)
STARBUCKS COFFEE COMPANY| business optimization product manager
-
Thanks @Kelly Drake, I tried your formula (adapted for my column names) and I'm getting an unparseable error. It's probably an error I injected when adapting it, but I can't seem to find the issue:
=IF(NOT(ISBLANK([Additional Eval Question #1]1)), “Completed”, IF(NOT(ISBLANK([Worker Eval Question #1]1)), “In progress”, “Awaiting worker response“))
p.s. I agree with what you said about the limitations with using the hidden field on the form. It was a good idea though, I might use it in the future for other workflows.
-
Hi @sschreiner
Just to jump in here - you adjusted the formula correctly but I can see that your quotation marks are curved, like so: ” instead of straight as they appear in Smartsheet, like so: "
It's one small detail, but copying over certain types of quotation marks will cause formulas to error, as they're recognized as different symbols. Try the exact same formula, but re-type out the quotes directly into Smartsheet so it's the right symbol. 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Hi @sschreiner
Depending on your language version you may have to replace , with ; and/or " with '.
Hope it helped.
-
Thanks Genevieve, my tired eyes didn't notice that! That did the trick :)
-
Ohe other edit I would suggest - ... Change the cell reference to @row... it helps optimize your formula/sheet.
https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell
Kelly Drake (she/her/hers)
STARBUCKS COFFEE COMPANY| business optimization product manager
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 409 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!