Automation with OR

Debbie Sawyer

With the Status issue. Can you set up a sheet with Status Number (1 through 15) and Status Description (with the wordy statements that you use for the Status values).

Then in your main sheet, get the Status Number in a helper column using the nested IF, then your current Status column would be a simple Index Match type function to index the status description from the status number within the reference table. Not having the wordy statements in the nested IF might give you enough characters to complete the function that way.

When I have run out of space on a complex nested IF before, I just split the function across 2 column, then used a third to select which of the first two had the value!

All good fun though - it wouldn't be satisfying when you get a result if it were always easy! ☺️

Debbie Sawyer

With the colours in the organigramme are you able to concatenate a symbol to the end of the data (an * or ' or something similar) then use conditional formatting in the organigramme sheet to say if a column contains a * then set the colour of the cell to Yellow, if it contains a ' set it to blue.

I am trying to think of an unobtrusive way for you to achieve your goal! This might not be suitable though!

KarineMul22_TPg

@Paul Newcome I have 14 checkboxes for 15 different statuses:

DO - Pending Design Review

DO - Drawing to release (Design Reviewed)

PE - Rework / Modification Scheme to put in place (Drawing released)

PE - MITP to be Approved (BAE) (Rework/Modif scheme in place)

PE - BoM to Update on system (MITP Approved)

SC - All parts to order (BoM updated)

SC - All parts to receive onsite (All parts ordered)

PE - Rework / Modif to complete (All parts received on site)

Systs - All specs to release (Rework/Modif completed)

QE - Supplier doc pack to complete (All specs released)

Ops - Prework to complete (Supplier doc pack complete)

PE - Build can Start? (Prework complete)

Build can Start

Build can Finish

Ops - Acceptance of WP launch

The logic is in order for the WP to have the Status of "PE - BoM to Update on system (MITP Approved)" for example, the design has to be reviewed, the drawing has to be released, the rework has to be in place and the MiTP has to be approved, but they don't need to be ticked in order, some might happen before the others, AND obviously, the parts need not to be all on order, otherwise the status would be "SC - All parts to order (BoM updated)".

I hope that makes sense

Thank you

Karine

Paul Newcome

Ok. So my suggestion would be to set up a table that has each status listed on its own row and then a series of checkboxes to replicate what you need checked for each status.

Then on both sheets you would have a text/number helper column that basically joins all of the checkboxes together into a string with a series of true/false.

On the main sheet it would look something like this:

=[1st Checkbox]@row + "" + [2nd Checkbox]@row

And on the helper sheet since all checkboxes will be together you could use a JOIN function:

=JOIN([1st Checkbox]@row:[Last Checkbox]@row)

This will output a string that looks something along the lines of

truetruefalsefalsetruefalse

You should have "true" for each checked box and "false" for each unchecked box in the order that the checkbox columns are set up in.

From there you can use an INDEX/MATCH to pull in the status with a match on this helper column

=INDEX({Status Table Status Column}, MATCH([Helper Column]@row, {Status Table Helper Column}, 0))

KarineMul22_TPg

@Debbie Sawyer Re colours in the organigramme, concatenating a symbol wouldn't work as the colour changes with the status. I think I might have found a solution:

I've added a column to each organigramme column with a link to the status, then do a conditional formatting depending on that status, and hide that column - it is quite laborious as for each hidden column I need the 15 colour/status options. I have proposed to the PM and printed the top 3 levels, awaiting to hear back from him

Thank you for your help, and if you think of another easier way please do let me know

Karine

KarineMul22_TPg

@Paul Newcome Hi Paul, so I have created a WP Tracker Statuses sheet:

Then i have added a Helper column as well on my main sheet:

so what's left for me to do is: From there you can use an INDEX/MATCH to pull in the status with a match on this helper column

=INDEX({Status Table Status Column}, MATCH([Helper Column]@row, {Status Table Helper Column}, 0))

but I 'm not clear on it, sorry, could you help again?

Many thanks

Karine

Paul Newcome

@KarineMul22_TPg You would create a formula in the Status column on the working sheet that references the table. The syntax would be exactly the same as what you have for the INDEX/MATCH. You will need to make sure you use the appropriate column name in the MATCH function (Helper@row instead of [Helper Column]@row) and you will need to create each {Cross Sheet Reference} as indicated.

Does that help, or is there a step that I need to expand on?

KarineMul22_TPg

@Paul Newcome Hi Paul, thank you very much for clarifying, and it works, mostly. I have a few #NO MATCH results as actually they do not match, and we're back to the same problem as on the working sheet the clicks are not in order so we have a true at the beginning then all false with a true in the middle, and in the reference table there's no such thing - does that mean that in the reference table I must do all the possibilities for any 1 status? that's going to be taking me a very long time!!!

KarineMul22_TPg

@Paul Newcome well actually they do not match, and we're back from the same problem as on the working sheet it not in order so we have a true at the beginning then all false with a true in the middle, and in the reference table there's no such thing - does that mean that in the reference table I must do all the possibilities for any 1 status?

Paul Newcome

@KarineMul22_TPg I'm not sure I follow. Can you provide more details? I thought there was only one set of boxes being checked for each status but that it didn't matter which order they were checked in?

KarineMul22_TPg

@Paul Newcome My apologies, your statement is true but there's more to it, for example, for the first Status "Do - Pending Design Review", we have all these possibilities and more:

As long as box 1 is ticked but not box 2 it will end up being that status whatever the number of boxes clicked from 3 onwards.

So for the second status, box 1 &2 would be clicked and box 3 NOT, whatever boxes 4/5/6etc click is

I hope this makes sense?

Apologies again

Debbie Sawyer

With this extra explanation, I would say that returning to a nested IF is the way forward. I appreciate you ran out of characters in the formula doing it this way, but if you just return a Status Number (1-14) then use a lookup in your table here to say Status 1 = DD Pending Design Phase, Status 2 = DD Drawing to release (Design Reviewed) etc then that will save a lot of characters.

If you still run out of characters then set up Helper Milestone checkboxes called 1-14 with the column formula just pointing at the corresponding check box.

Eg

Helper column "1" would have the formula =[DD Design review done if required]@row

Helper column "2" would have the formula =[DD - Drawings issued Boat 1]@row

So your nested formula In the Helper Column for Status Number would start as:

=IF(ISBLANK([1]@row),1,IF(ISBLANK([2]@row),2,IF(ISBLANK([3]@row),3,4))) etc

So this would work its way across the milestones looking for the first one to return blank, then that is the status. So it wouldn't matter if milestone 4 was ticked, if 2 wasn't ticked it would return status 2 until 2 was ticked then it would check 3, then 4 etc. So you might end up skipping a few status' (if they were done out of order) but it would return them from left to right. That appears to be what you are saying in the latest explanation. Although please forgive me if I have mis-understood!

Using Helper Column numbers for Milestone names and a Helper column Status Number will reduce the length of your Nested IF to one that should fit in the cell. Then just Index the Statuses column from your look up table using the Status Number to match against.

Paul Newcome

Ok. I think I understand.

Basically (for example) "Status 1" could be any of the below checkbox combinations:

1

2

3

1 & 2

1 & 3

2 & 3

1 & 2 & 3

In that case I agree with @Debbie Sawyer that some helper columns are in order. My suggestion would be a helper column for each status and having the individual IF/AND/OR formulas populating each one. Then there are a number of options that will allow you to pull the appropriate status from the helper columns.

The way I personally would do it would be to have all of the status helper columns next to each other and in order and the formulas outputting the actual status text then using something like this to pull in the appropriate status:

=INDEX([1st Helper]@row:[Last Helper]@row, COUNTIFS([1st Helper]@row:[Last Helper]@row, @cell <> ""))

The above is assuming that your statuses go in order and the boxes that are already checked for Status 2 would not become unchecked when shifting to Status 3.

KarineMul22_TPg

"Status 1" could be any of the below checkbox combinations:

1 / 1 &3 / 1&4&5 as long as 1 is ticked and 2 isn't

Status 2:

1 & 2 / 1 & 2 &4 / 1 & 2 &5... as long as 1 and 2 are ticked but not 3

Status 3: As long as 1 & 2 & 3 are ticked but not 4

Status 4: As long as 1 & 2 & 3 & 4 are ticked but not 5

etc

Paul Newcome

We may be able to simplify this. Can you write out each of the statuses the way you have above?

The way you have it written above, we could use something like

=IF([Checkbox1]@row = 1, IF([Checkbox2]@row = 1, IF([Checkbox3]@row = 1, IF([Checkbox4]@row = 1, "Status 4"), "Status 3"), "Status 2"), "Status 1")