Formula to change status based on sheet value

Jerry Alexander

I'm trying to change a status (from a dropdown box in sheet summary, based on a date field. When a check box checked the status would change.

Sheet Summary Field is "Construction Status", drop down list is:

• Not Started
• Permitting In Progress
• Permitted Inventory
• Slab Starts In Progress
• Slab Starts Inventory
• Vertical Construction
• Completed Inventory
• Closed

Each of these would be changed based on when a particular "Task" is completed.

Sheet Summary field would default to "Not Started"

Sheet Summary Status would change to "Permitting in Progress" when task "Permit Prep (Docs to Runner)" is "Started" (checkbox field).

Sheet Summary Status would change to "Permitted Inventory" when task "Receive Permit" is "Done" (Checkbox field) is checked.

So on and so on...... through the rest of the list in the construction status dropdown options.

If someone can help get this started, I can probably finish it out. Any help would be appreciated.

Andrée Starå

@Jerry Alexander

Try this and continue the pattern, so the last option is first.

=IF(AND([Task Name]7 = "Receive Permit", Done7 = 1), "Permitted Inventory", IF(AND([Task Name]2 = "Permit Prep (Docs to Runner)", Started2 = 1), "Permitting in Progress", "Not Started"))

Make sense?

Andrée Starå

Hi @Jerry Alexander

I hope you're well and safe!

Here's one way.

Try something like this. (You'll have to change the order and cell references, and when all is included and nothing is true, it will default to Not Started)

=IF(AND(Task1 = "Permit Prep (Docs to Runner)", Started1 = 1), "Permitting in Progress", IF(AND(Task2 = "Receive Permit", Done2 = 1), "Permitted Inventory", "Not Started"))

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Jerry Alexander

Thank you!

I'm having trouble. I tried converting your formula to match my sheet, but I messed up something....

The Sheet row / cell is Permit Prep (Docs to Runner). This task/row has a started column which is a checkbox field. When the started column is checked for this row, the Construction status field in the sheet summary would change to Permitting in Progress......

See my attempt.

=IF(AND([Permit Prep (Docs to Runner)], Started = 1), [Permitting in Progress], IF(AND([Receive Permit], Done = 1), [Not Started]))

Andrée Starå

@Jerry Alexander

Happy to help!

You have to reference the actual row with the different Tasks.

Something like this. (bolded is the actual column name and rows that you need to change)

=IF(AND(Task1 = "Permit Prep (Docs to Runner)", Started1 = 1), "Permitting in Progress", IF(AND(Task2 = "Receive Permit", Done2 = 1), "Permitted Inventory", "Not Started"))

Make sense?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Jerry Alexander

Yes, Im getting there! Its kind of working...

=IF(AND([Task Name]2 = "Permit Prep (Docs to Runner)", Started2 = 1), "Permitting in Progress", IF(AND([Task Name]7 = "Receive Permit", Done7 = 1), "Permitted Inventory", "Not Started"))

When neither "Started" or "done" are checked on either task, the field reflects "Not started". This is the desired result. When "Permit Prep (Docs to Runner)" is checked (started), the field changes to "Permitting in Progress". This part is working as intended. However, when I check "done" on the "Receive permit" task, the field remains "Permitting in Progress".

Not sure how to "order" the formula to override the previous

Andrée Starå

@Jerry Alexander

Try this and continue the pattern, so the last option is first.

=IF(AND([Task Name]7 = "Receive Permit", Done7 = 1), "Permitted Inventory", IF(AND([Task Name]2 = "Permit Prep (Docs to Runner)", Started2 = 1), "Permitting in Progress", "Not Started"))

Make sense?

Jerry Alexander

Awesome! Thanks for your help!

Andrée Starå

@Jerry Alexander

Excellent!

You're more than welcome!