Status formula pulling from 2 columns and using multiple conditions
Hello,
I am trying to write a formula that pulls from two columns: Progress and Done?
Progress is a drop down column with these options: Not started, In progress, Scheduled, Declined.
Done? is a checkbox.
When Done? is checked, then the formula should return Complete.
When Done? is unchecked and Progress is Not started, then the formula should return Not started.
When Done? is unchecked and Progress is In progress, then the formula should return In progress.
When Done? is unchecked and Progress is Scheduled, then the formula should return In progress.
When Done? is unchecked and Progress is Declined, then the formula should return Complete.
This is what I have so far. It works when Progress is "Scheduled" but not the others.
=IF(Progress@row = "Scheduled", IF([Done?]@row = 1, "Complete", IF(Progress@row = "In progress", "In progress", IF(Progress@row = "Scheduled", "In progress", IF(Progress@row = "Declined", "Complete", "Not started")))))
Thank you for your help!
Answers

Hi @Brooks
You'll want to use an OR function in there instead of moving from one IF to another. This way it checks the two possible instructions (checked box or "Declined") to then show "Complete":
=IF(OR(Progress@row = "Declined", [Done?]@row = 1), "Complete",
Then since we've already written the Checkbox instruction, the formula will only move on if the checkbox is not checked, so we don't have to write this again.
What I would do next is write the one logic statement that's different than the value:
IF(Progress@row = "Scheduled", "In progress",
Otherwise, the other two statements are to return the same value as in your "Progress" column, so you can simply reference that cell without an IF statement!
Progress@row
Full Formula:
=IF(OR(Progress@row = "Declined", [Done?]@row = 1), "Complete", IF(Progress@row = "Scheduled", "In progress", Progress@row))
Let me know if this makes sense and works for you 🙂
Genevieve

Hi @Genevieve P. !
Yes, I completely understand. I totally forgot about the OR function. Thanks so much! 😊
Help Article Resources
Categories
Check out the Formula Handbook template!