Multiple IF/AND Formula with Symbols to indicate project status values (not date driven)

Lisa Matthews

Hello,

I am looking for help to create a formula with multiple if/and conditions using copy/paste emojis in a dropdown list column type. I started out with a formula which I have used successfully before but will not work in this case because the new formula I need is not date driven at all and I broke it as I was trying to change it anyway.

Below is a picture of the broken formula, symbols and columns. The symbols do not copy well into the page I used for the image but they paste very easily into the Smartsheet column properties window and when the formula is correct, they appear clearly in the cell.

Thank you in advance for your help. Formulas are my biggest stumbling block and I appreciate this amazing community so much.

--Lisa M.

****

Nick Korna

Hi @Lisa Matthews,

I'm not sure you need any AND statements in your formula as it's currently trying to look at the Status column twice and from the data I can see it looks like this is a single option.

This formula would give you the right outcome (I think):

=IF(Status@row = "Location Completed", "Black square", IF(Status@row = "Location Not Started", "Blue Square", IF(Status@row = "Location Troubleshooting", "Red Circle", IF(Status@row = "Location in Progress", "Green Circle", IF(Status@row = "At Risk", "Yellow Circle", IF(Status@row = "Not Scheduled", "Purple Square", ""))))))

Change the symbol text for the actual required symbols and it should be good to go.

If the status can have multiple options at once, can you advise what the combinations would be in order to help you with an IF/AND formula?

Hope this helps, if you have any questions/comments then just post! 😊

Nick Korna

Hi @Lisa Matthews,

I'm not sure you need any AND statements in your formula as it's currently trying to look at the Status column twice and from the data I can see it looks like this is a single option.

This formula would give you the right outcome (I think):

=IF(Status@row = "Location Completed", "Black square", IF(Status@row = "Location Not Started", "Blue Square", IF(Status@row = "Location Troubleshooting", "Red Circle", IF(Status@row = "Location in Progress", "Green Circle", IF(Status@row = "At Risk", "Yellow Circle", IF(Status@row = "Not Scheduled", "Purple Square", ""))))))

Change the symbol text for the actual required symbols and it should be good to go.

If the status can have multiple options at once, can you advise what the combinations would be in order to help you with an IF/AND formula?

Hope this helps, if you have any questions/comments then just post! 😊

Lisa Matthews

Good Morning @Nick Korna !

Thank you so much for your help! The formula worked perfectly!! You are absolutely correct about the single option from the status column. A row will not have two separate statuses at the same time. I can't thank you enough for writing this formula for me. You are awesome!

--Lisa M.

Nick Korna

No problem, happy to have helped! 😊