Change Value in Symbol Column Based on Value of Other Column

Hello everyone!

I have a Status column (Symbol type), where users can choose manually the desired status.

I also have a Completed% column (Text/Number type), where a percentage is entered manually.

I would like to change the value of the Status column to "Blue" when Completed% is "100%", but keep as is in all other percentage values.

Examples:

  1. Status is Green, Completed% changes to "100%", then Status should change to Blue.
  2. Status is Green, Completed% changes to "80%", then Status should remain Green.

I have tried implementing this with a Workflow, but it is not possible to select the Status column as its type is Symbol.

I also cannot use a Column Formula on the Status column, as then the users won't be able to select the status from the list.

I would appreciate your ideas and support!

Answers

  • Colleen Patterson
    Colleen Patterson ✭✭✭✭✭✭
    edited 03/14/24

    You will want to run this as a formula in the status column:

    =IF([Completed%]@row = 1, "Blue", IF([Completed%]@row > .8, "Green", XXXXX))

    In this you would want to define with XX what you want to happen if neither of those values are met.

    You will want to make sure that the completed% column if formatted as % for the way I have written my formula.

    If my answer helped you, please be sure to mark it as Accepted to help future learners locate the information.

  • moraki
    moraki ✭✭
    edited 03/14/24

    Hello Colleen,

    Thank you for your reply.

    As I said, creating a Column Formula in the Status column is not a possible solution, as this would mean that the users will not be able to add values on their own.

    Do you have any other suggestions that might help with my case? My request is to change the value of Status column automatically to Blue when Complete% is 100%. In all other cases, the value of Status should not change. At the same time the user should be able to select manually the values of the Status column.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!