Simplifying text to another column

Hello! I think I need some sort of IF/OR/AND/THEN formula for what I'm working on. I am working in a sheet that has a status column with 8 different text values. My team wants to simplify the look of this in our external dashboard, so are creating a new column that changes the 8 distinct text values into 4 different distinct text values. I've played around with some IF/THEN formulas to get just one value "New" to populate to no success. I am also wondering if you could clear up if you can reference the same column multiple times in a formula. I have attached a photo that lays out the values I have and want to simplify to below.


Screenshot (79).png


Best Answer

  • KPH
    KPH Community Champion
    Answer βœ“

    Hi @Fiona Murray

    You can use a combination of IF and OR.

    This should work.

    =IF(OR([Dashboard Status]@row = "New", [Dashboard Status]@row = "Assigned"), "New", (IF(OR([Dashboard Status]@row = "In Progress", [Dashboard Status]@row = "Awaiting Review", [Dashboard Status]@row = "Delivered"), "In Progress", (IF(OR([Dashboard Status]@row = "Closed", [Dashboard Status]@row = "Cancelled"), "Closed", (IF([Dashboard Status]@row = "On Hold", "On Hold", "Error Message Here")))))))

    Here it is in use (and in color)

    image.png


    Explanation

    I build these in stages so you can troubleshoot as you go along. Start with

    =IF(OR([Dashboard Status]@row = "New", [Dashboard Status]@row = "Assigned"), "New", "not done yet")

    This puts in "New" if Dashboard Status is New or Assigned. And puts "not done yet" if it is not. Like this.

    image.png

    Then replace the "not done yet" with the next IF OR, like this:

    =IF(OR([Dashboard Status]@row = "New", [Dashboard Status]@row = "Assigned"), "New", (IF(OR([Dashboard Status]@row = "In Progress", [Dashboard Status]@row = "Awaiting Review", [Dashboard Status]@row = "Delivered"), "In Progress", "also not done yet")))

    To get this

    image.png

    Then replace the "also not done yet" with the next IF OR, like this:

    =IF(OR([Dashboard Status]@row = "New", [Dashboard Status]@row = "Assigned"), "New", (IF(OR([Dashboard Status]@row = "In Progress", [Dashboard Status]@row = "Awaiting Review", [Dashboard Status]@row = "Delivered"), "In Progress", (IF(OR([Dashboard Status]@row = "Closed", [Dashboard Status]@row = "Cancelled"), "Closed", "still not done yet")))))

    image.png

    You could then replace "still not done yet" with "On Hold" (so anything that is not New, Assigned, In Progress, Awaiting Review, Delivered, Closed, or Cancelled will be "On Hold"). But for safety I prefer to use a final IF and put an error message or blank for any row that doesn't match any of the criteria.


    =IF(OR([Dashboard Status]@row = "New", [Dashboard Status]@row = "Assigned"), "New", (IF(OR([Dashboard Status]@row = "In Progress", [Dashboard Status]@row = "Awaiting Review", [Dashboard Status]@row = "Delivered"), "In Progress", (IF(OR([Dashboard Status]@row = "Closed", [Dashboard Status]@row = "Cancelled"), "Closed", (IF([Dashboard Status]@row = "On Hold", "On Hold", "Error Message Here")))))))

    image.png

    If you make this a column formula you should be all set. I hope so!

Answers

  • KPH
    KPH Community Champion
    Answer βœ“

    Hi @Fiona Murray

    You can use a combination of IF and OR.

    This should work.

    =IF(OR([Dashboard Status]@row = "New", [Dashboard Status]@row = "Assigned"), "New", (IF(OR([Dashboard Status]@row = "In Progress", [Dashboard Status]@row = "Awaiting Review", [Dashboard Status]@row = "Delivered"), "In Progress", (IF(OR([Dashboard Status]@row = "Closed", [Dashboard Status]@row = "Cancelled"), "Closed", (IF([Dashboard Status]@row = "On Hold", "On Hold", "Error Message Here")))))))

    Here it is in use (and in color)

    image.png


    Explanation

    I build these in stages so you can troubleshoot as you go along. Start with

    =IF(OR([Dashboard Status]@row = "New", [Dashboard Status]@row = "Assigned"), "New", "not done yet")

    This puts in "New" if Dashboard Status is New or Assigned. And puts "not done yet" if it is not. Like this.

    image.png

    Then replace the "not done yet" with the next IF OR, like this:

    =IF(OR([Dashboard Status]@row = "New", [Dashboard Status]@row = "Assigned"), "New", (IF(OR([Dashboard Status]@row = "In Progress", [Dashboard Status]@row = "Awaiting Review", [Dashboard Status]@row = "Delivered"), "In Progress", "also not done yet")))

    To get this

    image.png

    Then replace the "also not done yet" with the next IF OR, like this:

    =IF(OR([Dashboard Status]@row = "New", [Dashboard Status]@row = "Assigned"), "New", (IF(OR([Dashboard Status]@row = "In Progress", [Dashboard Status]@row = "Awaiting Review", [Dashboard Status]@row = "Delivered"), "In Progress", (IF(OR([Dashboard Status]@row = "Closed", [Dashboard Status]@row = "Cancelled"), "Closed", "still not done yet")))))

    image.png

    You could then replace "still not done yet" with "On Hold" (so anything that is not New, Assigned, In Progress, Awaiting Review, Delivered, Closed, or Cancelled will be "On Hold"). But for safety I prefer to use a final IF and put an error message or blank for any row that doesn't match any of the criteria.


    =IF(OR([Dashboard Status]@row = "New", [Dashboard Status]@row = "Assigned"), "New", (IF(OR([Dashboard Status]@row = "In Progress", [Dashboard Status]@row = "Awaiting Review", [Dashboard Status]@row = "Delivered"), "In Progress", (IF(OR([Dashboard Status]@row = "Closed", [Dashboard Status]@row = "Cancelled"), "Closed", (IF([Dashboard Status]@row = "On Hold", "On Hold", "Error Message Here")))))))

    image.png

    If you make this a column formula you should be all set. I hope so!

  • Fiona Murray
    Fiona Murray ✭✭✭

    Thank you so much this worked!!

  • KPH
    KPH Community Champion

    Wonderful! Glad to have helped. 😍

    If you can, please click β€œyes” next to Did this answer the question?Β on the answer so others know it is solved.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!