Automation with OR

KarineMul22_TPg

Tried, but must be missing something as coming up #UNPARSEABLE

=if([DO - Design review done if required]@row, 1, IF([DO - Drawings Issued Boat 1]@row,1, IF([PE - Rework/Modification scheme in place]@row, 1, IF([PE - MITPs approved or N/A]@row, 1, IF([PE - BOM in line with B1 released dwgs]@row,1, IF([SC - All parts on order?]@row,1, IF([SC - All material on site?]@row,1, IF([PE - Rework/Modification complete ]@row, IF([Systs - Specs (ETS/ESS/ECS)/SPP (all) issued B1]@row, 1, [Qual - Material certs & docs Acceptable]@row, 1, IF([Ops - Pre-work completed (ESSs)?]@row, 1, IF([Ops - Acceptance of WP launch]@row,1, IF([PE - Build can start]@row, 1, IF([PE - WP can finish]@row, 1, "Build can Finish"), "Build can Start"), "Ops - Acceptance of WP launch?"), "PE - Build can start?"), "Ops - Prework to complete (Supplier doc pack complete)"), "QE - Supplier doc pack to complete (All specs released)"), "Systs - All specs to release (Rework/Modif completed)"), "PE - Rework/Modif to complete (All parts received on site)"), "SC - All parts to receive on site (all ordered)"), "SC - All parts to order (BoM Updated)"), "PE - BoM to update on system (MITP Approved)"), "PE - MITP to approved (BAE)"), "PE - Rework/Modif scheme to be put in place (drawing released)"), "DO - Drawing to release (Design reviewed)"), "DO - Pending Design Review")

KarineMul22_TPg

with the last bracket also - it seems like to many clicks not enough status

Paul Newcome

I'm not sure I follow...

KarineMul22_TPg

=if([DO - Design review done if required]@row, 1, IF([DO - Drawings Issued Boat 1]@row,1, IF([PE - Rework/Modification scheme in place]@row, 1, IF([PE - MITPs approved or N/A]@row, 1, IF([PE - BOM in line with B1 released dwgs]@row,1, IF([SC - All parts on order?]@row,1, IF([SC - All material on site?]@row,1, IF([PE - Rework/Modification complete ]@row, IF([Systs - Specs (ETS/ESS/ECS)/SPP (all) issued B1]@row, 1, [Qual - Material certs & docs Acceptable]@row, 1, IF([Ops - Pre-work completed (ESSs)?]@row, 1, IF([Ops - Acceptance of WP launch]@row,1, IF([PE - Build can start]@row, 1, IF([PE - WP can finish]@row, 1, "Build can Finish"), "Build can Start"), "Ops - Acceptance of WP launch?"), "PE - Build can start?"), "Ops - Prework to complete (Supplier doc pack complete)"), "QE - Supplier doc pack to complete (All specs released)"), "Systs - All specs to release (Rework/Modif completed)"), "PE - Rework/Modif to complete (All parts received on site)"), "SC - All parts to receive on site (all ordered)"), "SC - All parts to order (BoM Updated)"), "PE - BoM to update on system (MITP Approved)"), "PE - MITP to approved (BAE)"), "PE - Rework/Modif scheme to be put in place (drawing released)"), "DO - Drawing to release (Design reviewed)"), "DO - Pending Design Review")

Paul Newcome

Ok. Your syntax is a bit off. It should be if [column name]@row = 1.

=IF([DO - Design review done if required]@row = 1, IF([DO - Drawings Issued Boat 1]@row = 1, IF([PE - Rework/Modification scheme in place]@row = 1, IF(....................................................

Let's start with that update and see where we get.

Debbie Sawyer

@KarineMul22_TPg @Paul Newcome

Been catching up on yesterdays update. Has this got too complicated?

This is what I was suggested using ISBLANK going across the row, but ISBLANK didn't work, so If the box=0 then that is the status, irregardless of the next milestones. With this formula the status is #1 until column 1 is ticked, then status is 2 until column 2 is ticked, etc....

Have I missed something?

KarineMul22_TPg

Hi both,

Debbie, yes a bit complicated, Paul's suggestion seems simpler but at present I haven't got it right either!!

=IF([DO - Design review done if required]@row= 1, IF([DO - Drawings Issued Boat 1]@row=1, IF([PE - Rework/Modification scheme in place]@row= 1, IF([PE - MITPs approved or N/A]@row= 1, IF([PE - BOM in line with B1 released dwgs]@row=1, IF([SC - All parts on order?]@row=1, IF([SC - All material on site?]@row=1, IF([PE - Rework/Modification complete ]@row=1, IF([Systs - Specs (ETS/ESS/ECS)/SPP (all) issued B1]@row=1, IF([Qual - Material certs & docs Acceptable]@row= 1, IF([Ops - Pre-work completed (ESSs)?]@row= 1, IF([PE - Build can start?]@row= 1, IF([Ops - Acceptance of WP launch]@row=1, IF([PE - WP can finish?]@row= 1, "Build can Finish"), "Build can Start"), "Ops - Acceptance of WP launch"),"PE - Build can start? (Prework complete)"), "Ops - Prework to complete (Supplier doc pack complete)"), "QE - Supplier doc pack to complete (All specs released)"), "Systs - All specs to release (Rework/Modif completed)"), "PE - Rework/Modif to complete (All parts received on site)"), "SC - All parts to receive on site (all ordered)"), "SC - All parts to order (BoM Updated)"), "PE - BoM to update on system (MITP Approved)"), "PE - MITP to approved (BAE)"), "PE - Rework/Modif scheme to be put in place (drawing released)"), "DO - Drawing to release (Design reviewed)"), "DO - Pending Design Review")

I have checked each condition vs each status, and I cannot see either an extra bracket or one missing :(

Debbie Sawyer

Try

=IF([DO - Design review done if required]@row= 1, "DO - Pending Design Review", IF([DO - Drawings Issued Boat 1]@row=1, "DO - Drawing to release (Design reviewed)", IF([PE - Rework/Modification scheme in place]@row= 1, "PE - Rework/Modif scheme to be put in place (drawing released)", IF([PE - MITPs approved or N/A]@row= 1,"PE - MITP to approved (BAE)", IF([PE - BOM in line with B1 released dwgs]@row=1, "PE - BoM to update on system (MITP Approved)", IF([SC - All parts on order?]@row=1,"SC - All parts to order (BoM Updated)", IF([SC - All material on site?]@row=1,"SC - All parts to receive on site (all ordered)", IF([PE - Rework/Modification complete ]@row=1, "PE - Rework/Modif to complete (All parts received on site)", IF([Systs - Specs (ETS/ESS/ECS)/SPP (all) issued B1]@row=1, "Systs - All specs to release (Rework/Modif completed)",IF([Qual - Material certs & docs Acceptable]@row= 1, "QE - Supplier doc pack to complete (All specs released)",IF([Ops - Pre-work completed (ESSs)?]@row= 1, "Ops - Prework to complete (Supplier doc pack complete)", IF([PE - Build can start?]@row= 1, "PE - Build can start? (Prework complete)", IF([Ops - Acceptance of WP launch]@row=1,"Ops - Acceptance of WP launch", IF([PE - WP can finish?]@row= 1, "Build can Finish"), "Build can Start"))))))))))))))

My understanding of Nested if's Syntax is:

=IF(expression,True part, False part) which means,

If the expression returns true (the first part up to the first comma) then set the value of the cell to the True Part (the bit between the first and second comma, otherwise perform the False Part (whatever is after the second comma).

So

=IF([DO - Design review done if required]@row= 1 THEN Set the value of the cell to "DO - Pending Design Review" OTHERWISE do this IF([DO - Drawings Issued Boat 1]@row=1, (another question which then goes back to the start of the logic) so IF([DO - Drawings Issued Boat 1]@row=1 THEN set the value of the cell to "DO - Drawing to release (Design reviewed)" OTHERWISE do this IF([PE - Rework/Modification scheme in place]@row= 1, I.e. ask another question. You nest all your questions giving the instruction of what to do in the True part immediately, followed by what to do if it isn't true, which could be ask another question or set the false value. Finally count the number of IF's used and ensure there are that many close brackets at the end.

Apologies if you know all this, but the syntax of your nested if shared, to me, doesn't follow this logic.

After saying all this, I still have a feeling that this won't do what you said, if the boxes are ticked out of order, then the latest box in the process will take the status, so it won't stay on 1 until 2 is ticked even if 5 and 13 are ticked, if 13 is ticked but 2 isn't your function will return 13's Status as it doesn't consider previous ticks, which is why I suggested doing it on Not ticked boxes!! It will stay on the not ticked one until it is ticked (and ignore others!)

😁

KarineMul22_TPg

Thanks Debbie, still coming back as UNPARSEABLE though

Debbie Sawyer

Apologies

Try this

=IF([DO - Design review done if required]@row= 1, "DO - Pending Design Review", IF([DO - Drawings Issued Boat 1]@row=1, "DO - Drawing to release (Design reviewed)", IF([PE - Rework/Modification scheme in place]@row= 1, "PE - Rework/Modif scheme to be put in place (drawing released)", IF([PE - MITPs approved or N/A]@row= 1,"PE - MITP to approved (BAE)", IF([PE - BOM in line with B1 released dwgs]@row=1, "PE - BoM to update on system (MITP Approved)", IF([SC - All parts on order?]@row=1,"SC - All parts to order (BoM Updated)", IF([SC - All material on site?]@row=1,"SC - All parts to receive on site (all ordered)", IF([PE - Rework/Modification complete ]@row=1, "PE - Rework/Modif to complete (All parts received on site)", IF([Systs - Specs (ETS/ESS/ECS)/SPP (all) issued B1]@row=1, "Systs - All specs to release (Rework/Modif completed)",IF([Qual - Material certs & docs Acceptable]@row= 1, "QE - Supplier doc pack to complete (All specs released)",IF([Ops - Pre-work completed (ESSs)?]@row= 1, "Ops - Prework to complete (Supplier doc pack complete)", IF([PE - Build can start?]@row= 1, "PE - Build can start? (Prework complete)", IF([Ops - Acceptance of WP launch]@row=1,"Ops - Acceptance of WP launch", IF([PE - WP can finish?]@row= 1, "Build can Finish", "Build can Start")))))))))))))))

Debbie Sawyer

Remove the last bracket!! I can't count!

=IF([DO - Design review done if required]@row= 1, "DO - Pending Design Review", IF([DO - Drawings Issued Boat 1]@row=1, "DO - Drawing to release (Design reviewed)", IF([PE - Rework/Modification scheme in place]@row= 1, "PE - Rework/Modif scheme to be put in place (drawing released)", IF([PE - MITPs approved or N/A]@row= 1,"PE - MITP to approved (BAE)", IF([PE - BOM in line with B1 released dwgs]@row=1, "PE - BoM to update on system (MITP Approved)", IF([SC - All parts on order?]@row=1,"SC - All parts to order (BoM Updated)", IF([SC - All material on site?]@row=1,"SC - All parts to receive on site (all ordered)", IF([PE - Rework/Modification complete ]@row=1, "PE - Rework/Modif to complete (All parts received on site)", IF([Systs - Specs (ETS/ESS/ECS)/SPP (all) issued B1]@row=1, "Systs - All specs to release (Rework/Modif completed)",IF([Qual - Material certs & docs Acceptable]@row= 1, "QE - Supplier doc pack to complete (All specs released)",IF([Ops - Pre-work completed (ESSs)?]@row= 1, "Ops - Prework to complete (Supplier doc pack complete)", IF([PE - Build can start?]@row= 1, "PE - Build can start? (Prework complete)", IF([Ops - Acceptance of WP launch]@row=1,"Ops - Acceptance of WP launch", IF([PE - WP can finish?]@row= 1, "Build can Finish", "Build can Start"))))))))))))))

KarineMul22_TPg

It's working! Well, I need to change the status though as not in phase and then experiment with the clicks but least it's correct, thank you :)

Debbie Sawyer

@KarineMul22_TPg

My Last comment! Promise!!

If you find that the previous formula logic parses now but is returning the status of the highest tick, then this one will return the status of the lowest NOT ticked box...

=IF([DO - Design review done if required]@row= 0, "DO - Pending Design Review", IF([DO - Drawings Issued Boat 1]@row=0, "DO - Drawing to release (Design reviewed)", IF([PE - Rework/Modification scheme in place]@row= 0, "PE - Rework/Modif scheme to be put in place (drawing released)", IF([PE - MITPs approved or N/A]@row= 0,"PE - MITP to approved (BAE)", IF([PE - BOM in line with B1 released dwgs]@row=0, "PE - BoM to update on system (MITP Approved)", IF([SC - All parts on order?]@row=0,"SC - All parts to order (BoM Updated)", IF([SC - All material on site?]@row=0,"SC - All parts to receive on site (all ordered)", IF([PE - Rework/Modification complete ]@row=0, "PE - Rework/Modif to complete (All parts received on site)", IF([Systs - Specs (ETS/ESS/ECS)/SPP (all) issued B1]@row=0, "Systs - All specs to release (Rework/Modif completed)", IF([Qual - Material certs & docs Acceptable]@row= 0, "QE - Supplier doc pack to complete (All specs released)", IF([Ops - Pre-work completed (ESSs)?]@row= 0, "Ops - Prework to complete (Supplier doc pack complete)", IF([PE - Build can start?]@row= 0, "PE - Build can start? (Prework complete)", IF([Ops - Acceptance of WP launch]@row=0,"Ops - Acceptance of WP launch", IF([PE - WP can finish?]@row= 0, "Build can Finish?", "Build Finished"))))))))))))))

KarineMul22_TPg

Hi Debbie, now this is the one, many many thanks!

Debbie Sawyer

@KarineMul22_TPg

Yay!! Well done!! ha ha