Parent status (RYGB ball) changing based on child status (dropdown)

h1pst3r

I am looking for help with a formula where I have a parent row and then multiple children rows below. The Parent has an overall status that is a 4 ball symbol of red, yellow, green, blue)

My child rows are using a dropdown menu called "Confidence" with percentages as the values (10%, 20%...100%).

I'd like to set the the ball color for the parent row to be based on the highest percentage of any of the children. 

If the highest child is row is set  to 90 or 100% the parent should be blue.

If the highest child row is set to 70 or 80% the parent should be green.

If the highest child row is set to 50 or 60% the parent should be yellow.

If the highest child row is set from 10 to 40% the parent should be red. 

Thanks in advance for any assistance!

-Chris

Paul Newcome

The first question to ask would be... Is the column formatted for percentages, or is it basic text with the ranges in the Value portion of the column properties? Both can be done pretty easily, but the solutions for each are very different.

h1pst3r

Thanks for the reply, Paul.

The child column is a drop-down with text that I entered.

These are the values in the drop down:

10%

20%

30%

40%

50%

60%

70%

80%

90%

100%

Thanks!

 

Mike McNamara

This is interesting. I like that the solution seems pretty easy, Paul. I'll follow this to see what the solution would look like. I've only ever done counts and such for CHILDREN, never a logic thing like this.

Paul Newcome

Ok. And the last question would be in reference to "highest child row". If row 1 is your parent and rows 2 - 5 are your child rows, do you mean the highest as in row 2 or the highest as in highest percent complete? This will determine exactly what formula we use to pull the correct color.

 

In either case, you will need to build a table as in the image below. If you build it on the same sheet, you can stuff it all the way to the right and Hide the columns once everything is set. If you are regularly sorting/adding/deleting rows that might affect the table, you can build it on another sheet, and we can use some simple cross-sheet references.

h1pst3r

Paul, 

"do you mean the highest as in row 2 or the highest as in highest percent complete?" 

I mean the latter, the highest as in percent complete.

I will build the table now. The suspense is half the fun here, thanks, Paul!!

-Chris

Paul Newcome

Chris,

 

I made a minor tweak to the table and my post. Forget about the "quotes" in the table.

 

Just let me know whether it will be on the same sheet or a different sheet.

h1pst3r

Paul,

My sheet has several sections of these parent/child clusters, and yes new rows of children are added and removed.

Think about the parent child clusters like this where we are looking for property across a state's counties:

Massachusetts County 1 (Parent) -- BGYR ball overall status set for [match]

-Property 1 (child) -- 20% [confidence]

-Property 2 (child) -- 80% [confidence]

-Property 3 (child) -- 40% [confidence]

*[column name]

So, the formula is looking at the confidence level of the children in order to give an overall status rating denoted by the color of the ball. On a single sheet there would be multiple parents (multiple counties in a state) and multiple children within a parent (properties within a county); therefore multiple parent/child clusters within one sheet. We then have these sheets for all the states in which we work.

Basically, I am trying to us this logic to drive a report that shows where we don't have a county with an actionable property (which would be determined by low confidence percentage which sets the ball color in the parent. I'm doing this in order to focus our efforts in counties where we need to find a property.

So, it may make sense to do this in a separate sheet so that multiple "state" sheets can reference it.

Hopefully that all makes sense.

-Chris

Paul Newcome

It does make sense, and this formula will work in all parent rows on the same sheet regardless of how many children there are or how many "clusters" there are within the sheet.

 

So the sheet with the table on it we will call "Table Sheet" for this example. The sheet where you will be putting the formula I will call "Formula Sheet".

.

The references from the formula are explained here:

{Table Sheet Range 1}: The Color column from the table you just built.

{Table Sheet Range 2}: The Percentage column from the table you just built.

NOTE: To get the ranges above, as you come to that portion of the formula you will see a link that says "Reference another sheet" in the formula help box. Click on that, select the appropriate sheet, select the appropriate range, and then click "OK".

[Confidence Column Name]: The name of the column on the "Formula Sheet" where the dropdown list is.

NOTE: "@row" replaces your row number. You can leave it as is in the formula below and it will automatically reference whatever row the formula is in. That way you don't have to worry about updating row numbers when you copy/paste it to the rest of the parent rows.

.

Here's your formula...

 

=INDEX({Table Sheet Range 1}, MATCH(MAX(CHILDREN([Confidence Column Name]@row)), {Table Sheet Range 2}, 0))

.

.

When using an INDEX/MATCH (better than VLOOKUP in my opinion for numerous reasons), here's a link that gives a breakdown of how it works for future reference...

 

https://community.smartsheet.com/discussion/most-recent-form-entries-metrics#comment-112311

(5th post from the top)

 

h1pst3r

*UPDATE: saw your comment: "when you paste to the rest of the parent rows"

Still reading through this, Paul, but one quick question:

On the formula sheet, should I be pasting the formula to only the [match] column of the parent row, or to the parent and the children rows for each cluster.

This is awesome, I owe you a beer (or several!)

-Chris

Paul Newcome

The formula will go in the column you have set for the RYGB in each of the parent rows. If you put it in a row that has no child rows, you will receive an error. If the row has at least one child row underneath of it (even if it is itself a child of another row) slap the formula on in there.

Just make sure its in the RYGB column and has at least one child row, and you'll be set.

h1pst3r

THAT. IS. AWESOME!

I need to mess with some formatting in order to get a clean parent/child relationship (I was using some dates under the parent that screws up the clean hierarchy between the parent and children) but I can figure that out.

This is really terrific. Going to PM you in a bit about future work if you have available cycles periodically.

Thanks! 

Mike McNamara

Excellent solution Paul. I created an example sheet just to tuck this away should the need arise in the future. Thanks for sharing!