Simplifying text to another column

Options

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.



Best Answer

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓
    Options

    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)


    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.

    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

    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")))))

    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")))))))

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

Answers

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓
    Options

    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)


    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.

    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

    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")))))

    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")))))))

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

  • Fiona Murray
    Options

    Thank you so much this worked!!

  • KPH
    KPH ✭✭✭✭✭✭
    Options

    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!