Simplifying text to another column

Fiona Murray · 11/02/23

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.

KPH · 11/03/23

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!

KPH · 11/03/23