How if a box is checked it any point in the process it will mark it checked for that section

Jett

I have sheet that has many items on one that all have a similar process. If one is marked sold at any point (any child step) it will check all the sold for that particualr parent and children. This will stop the notification for that particular item. I do not want it to check the boxes for the whole column because it will stop the notification for items that are still not sold and still need other steps.

Screenshot 2024-12-10 at 1.33.08 PM.png

Kelly Moore

Hey @Jett

The rows at the bottom of your screenshot that are not highlighted. As shown, they are children of the top row SN. I wondered if this is how the rest of your sheet is structured? Or are the SNs at the bottom supposed to also be outdented like row number 1? If they are outdented then the formula works as written

If the indentation is correct then I need a unique identifier to find the rows with the serial number. Do those rows actually include the words "Item Number" and/or "SN"? Could you share a screenshot of the actual syntax of those rows. You can use dummy alphanumeric characters but please use the same format as your actual data. The opportunity with your data (as shown in the screenshot) is trying to differentiate one parent row, or specific hierarchy level, from another. I understand the use of the helper column however we need something unique to find the SN row if it is not going to be a topmost parent.

Kelly

Kelly Moore

Hey @Jett

It will take a helper column to do this since there is already data being input in the [SOLD?] field. I called the column 'Grouped Sold'.

[Grouped Sold] =

=IF(COUNT(CHILDREN([Primary Column]@row)) > 0, MAX(IF(Sold@row = true, 1, 0), IF(COUNTIFS(CHILDREN(Sold@row), 1, CHILDREN([Primary Column]@row), <>"") > 0, 1, 0)), PARENT())

Will this work for you?
Kelly

Jett

Hi Kelly,

I am getting a unparseable error.

Kelly Moore

@Jett

Are the column names correct? Would you post a screenshot of the colored formula in a cell? Did you add a helper column to your sheet? The formula is working in my test sheet
Kelly

Jett

Hi @Kelly Moore

I have it working a little closer. I need it so when it is marked Sold at any point in the stages that it will mark it all sold for all the steps. It is only doing it for that section. I need it for everything under that particular Item Number/SN.

Kelly Moore

@Jett

If you change the new column to a checkbox column, you will see the checkmarks. Your original checkbox column [Sold?] will not be checked. You'll need to change your email alerts to the new column

As written, looking at the example in your screenshot above, this formula would only check boxes for your Social Media section. Are these the rows you needed checked? If not, what rows were you expecting- all of the ones shown?

Please @mention me when replying to make sure I receive the email

Kelly

Kelly Moore

Hey @Jett

In the event you wanted all of the DESCENDANTS/ANCESTORS checked off and not just one section of Parent/Children then use this formula instead. The formula is set up for a checkbox column.

=IF(COUNT(ANCESTORS([Primary Column]@row)) = 0, MAX(IF([Sold?]@row = true, 1, 0), IF(COUNTIFS(DESCENDANTS([Sold?]@row), 1, DESCENDANTS([Primary Column]@row), <>"") > 0, 1, 0)), PARENT())

Let me know if something needs tweaking

Kelly

Jett

Hi @Kelly Moore

This is great! This is working for that one. However We are going to have multiple on a sheet going at once. If I check one sold at any level it marks it sold for ALL of them on all items going at once. How do we make is so that each item is separate. So if one is marked sold all the steps for that specific item is marked not all of them on the sheet.

I think we need something that will assign all the children per parent have a number or something to relate them all in that section but I do not know how to do that or what the best way going about that is.

Thanks!

Kelly Moore

@Jett

May I see a screenshot of how your sheet is set up? From the top row to at least 2 part numbers. The rows can be collapsed - I'm interested in seeing how your levels are set up. The current formula will place checkmarks in all the Descendants of a top level Parent. I assumed each Part Number would be a top level Part Number and that you didn't have any higher Parents on the sheet. If possible, on your screenshot please circle the what you wish to be checked on two of your part numbers.

And yes, with a helper column we can put the Part Number on each row, which will also help you if you begin using Reports or set up cross sheet references to this sheet. Let me know if you want that

Kelly

Kelly Moore

Hey @Jett

I reread and need to clarify - may I see a screenshot, including some of the sheet hierarchy, where you have highlighted the rows you expect to see checked off. For example, if in your original screenshot you had a checkmark in Pictures (Under Machine), what rows would you expect to see checked? The formula above will take care of an entire Part Number section, and will handle multiple Part Numbers independently of each other

Kelly

Jett

Hi @Kelly Moore

I would need all of those in blue to check. This will stop automations from going out if it is sold at any point. However I cannot have it check for the duplicate ones that are not expanded.

Thanks!

Kelly Moore

hey @Jett

I don't see any checked columns in [Sold?] which is one reason why the [Grouped Sold] is blank. It would be helpful to see what the formula is actually doing in your sheet.

How would smartsheet know something is a duplicate, that is, what makes it a duplicate? With the 'duplicate' rows collapsed I cannot see them.

It usually takes a few helper columns to indicate duplicates. We can work that out.

Kelly

Jett

Hi @Kelly Moore,

What I have highlighted is what I will all need checked. Since all items numebr/SN will all have the same steps I cannot have the checking base off the step. I need it to be specific that that Item number/SN. So when a step in this particalr item is marked sold it will check them all and then I can make an automation that if it is check the upcoming/overdue does not notify. So I am thinking we need a helper column to label all the steps the same as what Item/SN is inputted by the user.

Since there will be many going at one I cannot have it check the entire column and this will stop the notification on ones that still need to be done.

Kelly Moore

Hey @Jett

The rows at the bottom of your screenshot that are not highlighted. As shown, they are children of the top row SN. I wondered if this is how the rest of your sheet is structured? Or are the SNs at the bottom supposed to also be outdented like row number 1? If they are outdented then the formula works as written

If the indentation is correct then I need a unique identifier to find the rows with the serial number. Do those rows actually include the words "Item Number" and/or "SN"? Could you share a screenshot of the actual syntax of those rows. You can use dummy alphanumeric characters but please use the same format as your actual data. The opportunity with your data (as shown in the screenshot) is trying to differentiate one parent row, or specific hierarchy level, from another. I understand the use of the helper column however we need something unique to find the SN row if it is not going to be a topmost parent.

Kelly

Kelly Moore

Hey @Jett

Were you able to resolve your issue?

Jett

Hi @Kelly Moore

Thank you so much for checking back on this! I think everything is working at the moment. Thank you SO much for all of your help!