Automatically updating Status column based on presence of data in other cells

sschreiner

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!

Kelly Drake

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.