Formula Trouble

Doug_aftgp.com

I'm trying to write a formula that will populate the status cell in a parent row based on priority of different statuses in the children's rows. Here is a copy of the formula I have wrote. =IF(HAS(CHILDREN(), "Quoted"), "Quoted", IF([% Complete]@row = 1, "Complete", IF([% Complete]@row = 0, "Not Started")))

This formula will populate "Quoted" if it is available in one of the children. What I need is for the formula to selected parts order if quoted is not available or if quote and parts ordered are not available the it will return Assembly/Install and if all three are not available then it will leave blank

The different statuses that do work well are "Complete", "Not Started" . The statues I need to prioritize will be as followed "Quoted", Parts Ordered", "Assembly/Install"

Thank you for any help

Julio S.

Hi Doug_aftgp.com, 

In order to have the Quoted, Parts Ordered, Assembly / Install and blank statuses you can try the following:

=IF(HAS(CHILDREN(), "Quoted"), "Quoted", IF(HAS(CHILDREN(), "Parts Ordered"), "Parts Ordered", IF(AND(ISBLANK(Status2), ISBLANK(Status3), ISBLANK(Status4)), " ", "Assembly / Install")))

Note that I left “Complete” and “Not Started” out of the equation with views to simplify the formula. If you'd like them to be considered in conjunction with the other statements, you may want to add additional arguments in the line of IF(AND(HAS(CHILDREN(), "Quoted"), % Complete = 1, "Complete")) to return “Complete” when "Quoted" is selected. As you can see, you'd need to add an additional IF(AND( statement for each pair of statuses that are meant to return "Complete" or "Not started" when prioritizing "Quoted", "Parts Ordered" and "Assembly / Install" over "Completed" and "Not Started". Please ensure that each of these arguments are defined prior to the single "Quoted" and "Parts ordered”statuses as the formula reads from left to right and once a condition is met it will stop reading the next ones.  

E.g, the following formula will bring return "Complete" in cases only where "quoted" and "completed" conditions are met:

=IF(AND(HAS(CHILDREN(), "Quoted"), % Complete = 1), "Complete”, IF(HAS(CHILDREN(), "Quoted"), "Quoted", IF(HAS(CHILDREN(), "Parts Ordered"), "Parts Ordered", IF(AND(ISBLANK(Status2), ISBLANK(Status3), ISBLANK(Status4)), " ", "Assembly / Install”))))

You may want to review AND, and ISBLANK functions if further information is needed about how to use them. To simplify this, you may want to define separate Columns for Complete / Not Started statuses from "Quoted" or give them priority by starting your formula with =IF(% complete@row = 1, “Complete” …)

 If you need further assistance to develop this formula, please include a screenshot of your sheet to offer a more visual insight. Please remember to hide any confidential information that shouldn't display.

 I hope this can be of help.

 Cheers,

 Julio

Julio S.

Hi Doug_aftgp.com, 

In order to have the Quoted, Parts Ordered, Assembly / Install and blank statuses you can try the following:

=IF(HAS(CHILDREN(), "Quoted"), "Quoted", IF(HAS(CHILDREN(), "Parts Ordered"), "Parts Ordered", IF(AND(ISBLANK(Status2), ISBLANK(Status3), ISBLANK(Status4)), " ", "Assembly / Install")))

Note that I left “Complete” and “Not Started” out of the equation with views to simplify the formula. If you'd like them to be considered in conjunction with the other statements, you may want to add additional arguments in the line of IF(AND(HAS(CHILDREN(), "Quoted"), % Complete = 1, "Complete")) to return “Complete” when "Quoted" is selected. As you can see, you'd need to add an additional IF(AND( statement for each pair of statuses that are meant to return "Complete" or "Not started" when prioritizing "Quoted", "Parts Ordered" and "Assembly / Install" over "Completed" and "Not Started". Please ensure that each of these arguments are defined prior to the single "Quoted" and "Parts ordered”statuses as the formula reads from left to right and once a condition is met it will stop reading the next ones.  

E.g, the following formula will bring return "Complete" in cases only where "quoted" and "completed" conditions are met:

=IF(AND(HAS(CHILDREN(), "Quoted"), % Complete = 1), "Complete”, IF(HAS(CHILDREN(), "Quoted"), "Quoted", IF(HAS(CHILDREN(), "Parts Ordered"), "Parts Ordered", IF(AND(ISBLANK(Status2), ISBLANK(Status3), ISBLANK(Status4)), " ", "Assembly / Install”))))

You may want to review AND, and ISBLANK functions if further information is needed about how to use them. To simplify this, you may want to define separate Columns for Complete / Not Started statuses from "Quoted" or give them priority by starting your formula with =IF(% complete@row = 1, “Complete” …)

 If you need further assistance to develop this formula, please include a screenshot of your sheet to offer a more visual insight. Please remember to hide any confidential information that shouldn't display.

 I hope this can be of help.

 Cheers,

 Julio