Status Updates

Amber Jackson

Good afternoon gurus! Here is our question of the week!

I have a process that follows a material through different stages of release. This process spans across multiple sheets, some sheets containing multiple steps in the process. I had a "status" column requiring manual updates, but time has shown that automation would be much better in this case.

I need help building a formula to show the correct status of a material at any stage of the process. I'm guessing an IF statement about a mile long will be in the works. Any ideas? Thanks in advance!

Here are the different statuses of the process:

Order Requested

Ordered by Procurement

Received by S/R

Submitted to QC

In-Testing (QC)

In Review (QC)

Released by QC

In Inventory

**Issues--Needs Attention

**Rejected

** These are "special cases" and wouldn't be part of the normal workflow.

Paul Newcome

Are you bale to provide the logic behind each status?

Amber Jackson

@Paul Newcome there are separate "triggers" for each status (most of which are checkboxes). For example, if the "ordered" box is checked, the status should read "Ordered by Procurement." The problem arises when trying to follow the statuses/triggers through the process. The trigger needed for "Released by QC" would also have all of the triggers fulfilled for the six previous statuses. Does that make sense?

Paul Newcome

Nested IF statements read from left to right and stop on the first true value. So in your case, you are going to want to write it backwards where the first IF is actually the last status. The second IF will be the second to last status, so on and so forth.

=IF([8th Column]@row = 1, "8th Status", IF([7th Column]@row = 1, "7th Status", IF([6th Column]@row = 1, "6th Status", .........................................

Amber Jackson

@Paul Newcome you're the best! I'll try this out today!

Paul Newcome

Happy to help. 👍️