Show all Children Health Ball Colors in Parent Cell?

clairehunter

Here's an example, if there are 3 children with different health statuses (see ss below). Is there any way to show all three colors in the parent field? Thanks!

JamesB

@clairehunter

This is a different approach than what I had to do, mine had many more columns and variables to deal with, however, just reading this it should be

=IF(COUNT(CHILDREN()) = 0, IF([B/O]@row = 1, "🔴", IF(Status@row = "In Progress", "🟡", IF(Status@row = "Not Started", "⚫️", IF(Status@row = "Completed", "🟢")))), JOIN(DISTINCT(COLLECT(CHILDREN(), CHILDREN(), @cell <> ""))))

This should say if it is a child row, then go through the color options. If it is not a child row then join the distinct colors together.

Paul Newcome

There is not.

Feel free to browse the Product Ideas top (link at the top of the page) to see if the idea has already been submitted. If it has, you can add your vote to it. If it has not, you can submit it yourself.

Either way, feel free to post a link to the Idea here in this thread so that other users that come across this one can easily add their votes as well.

JamesB

@clairehunter

This could be done through formulas, helper columns and a drop down list type column. The question would be though, is your ball color assignment created by a formula, or is it a manual selection? I am doing something similar on a task block/on hold tracking column.

Andrée Starå

Hi @clairehunter

I hope you're well and safe!

Unfortunately, it's not possible now, but it's an excellent idea!

Please submit this as a Product Feedback or Idea (If it hasn't been added already) when you have a moment.

Here's a possible workaround or workarounds

• Using Emojis or similar

Would that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

clairehunter

@JamesB

My ball color is assigned by a formula. Could you explain to me how you are doing it? Thanks so much!

clairehunter

Hello @Andrée Starå

I do believe an emoji work around would work. How would I go about setting this up? My ball color is already assigned to a formula. Thanks!

clairehunter

Okay. I am wondering if I could assign an emoji and do something like the ss below. How would I create the formula that would automatically populate parent fields with all of the values displayed in the children cells?

(Orange row is indented parent row.)

Paul Newcome

I forgot that the UNICHAR function has RYG symbols.

In your child row formula, replace "Red" with UNICHAR(128992), "Yellow" is UNICHAR(128993), and "Green" is UNICHAR(128994).

The the parent row formula would be

JOIN(DISTINCT(COLLECT(CHILDREN(), CHILDREN(), @cell <> "")))

And to combine the two for a column formula:

=IF(COUNT(CHILDREN()) = 0, child_row_formula, parent_row_formula)

clairehunter

Hello @Paul Newcome

This looks perfect! My only additional question would be, I am trying to make it a column formula as you mentioned above. I want it to be added to this preexisting column formula: (if that's possible, of course.)

=IF([B/O]@row = 1, "🔴", IF(Status@row = "In Progress", "🟡", IF(Status@row = "Not Started", "⚫️", IF(Status@row = "Completed", "🟢"))))

Thanks so much for your help!

Paul Newcome

You would use the syntax in my last comment.

=IF(COUNT(CHILDREN()) = 0, child_row_formula, parent_row_formula)

clairehunter

@Paul Newcome

Hello Paul,

I apologize, I am most likely doing something incorrectly, but it doesn't seem to be working. I add it to the beginning of my preexisting formula and it returns saying the syntax is not correct. I also tried in a column without a preexisting formula and it returns as #UNPARSEABLE.

Paul Newcome

Can you share what exactly you are trying?

Andrée Starå

@clairehunter

Excellent! Glad to see that you figured out how to use Emojis.

Happy to help!

I saw that Paul had answered already! (and that you've almost solved the complete formula)

Let me know if I can help with anything else!

Best,

Andrée

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

JamesB

@clairehunter

This is a different approach than what I had to do, mine had many more columns and variables to deal with, however, just reading this it should be

=IF(COUNT(CHILDREN()) = 0, IF([B/O]@row = 1, "🔴", IF(Status@row = "In Progress", "🟡", IF(Status@row = "Not Started", "⚫️", IF(Status@row = "Completed", "🟢")))), JOIN(DISTINCT(COLLECT(CHILDREN(), CHILDREN(), @cell <> ""))))

This should say if it is a child row, then go through the color options. If it is not a child row then join the distinct colors together.

clairehunter

@Paul Newcome

Okay, I will try to explain to the best of my ability, lol. I am creating an "Open Orders" sheet. I am wanting the employees who use this sheet to view strictly in card view with the ability to drag from "In Progress" to "Completed" to "Backorder", etc. When the "Status" field changes, I have a formula set up to automatically change the "Health" bubble to "🟡" for "In Progress", "🔴" for "Backorder", etc. Each order has a parent that is like the overhead summary of the order, the children are each item on the order. If one item has been started, I am wanting the parent to show the "🟡" bubble for "In Progress". Basically for every variation I want it to show up automatically in the parent row (as a column formula). I already have a formula in my "Health" column and am looking to add this new formula to add the "summary" to parent row to the preexisting column formula. I would like it to look like the ss below. (The "Health" column is where I want the formula and contains the preexisting: =IF([B/O]@row = 1, "🔴", IF(Status@row = "In Progress", "🟡", IF(Status@row = "Not Started", "⚫️", IF(Status@row= "Completed", "🟢")))) formula. The "Test" column displays how I would like it to look. The formula you posted (=JOIN(DISTINCT(COLLECT(CHILDREN(), CHILDREN(), @cell <> "")))) works, however, it doesn't work as a column formula (it erases all of the content that was already there). I'm bad at explaining, but I hope this helps clear things up. LMK if you need any more information or clarification. Thanks so much for your help and patience!

clairehunter

@JamesB

That works PERFECTLY! Thank you so so much!