Identifying which rows are CHILDREN

Our sheets and reports all run smoothly enough however where I spend most of my time is fixing indentations that correct PARENT / CHILDREN FUNCTIONS.

Our users don't pick up on the minor change in the indentation button in the menu bar which typically results in reports being generated down-stream returning figures that are lower than that displayed on a sheet.

Has anyone identified a way in which PARENT / CHILDREN functions auto-populate when new rows are added below a PARENT?

This issue is the single-handed biggest time-waster for me and the team to fix.

Best Answer

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi Richard,

    I would suggest setting up a Helper Column that indicates whether a row is a Parent or Child row. Then you can use Conditional Formatting rules to automatically adjust the visuals depending on what type of row it is.

    For example, you may have the Parent Row have larger, bolded text, and a different coloured background. Then as users add rows it will be obvious if it's a Parent or Child, since as soon as they indent the row will update:


    The formula in that helper checkbox would be as follows:

    =IF(COUNT(CHILDREN([Primary Column]@row)) >= 1, 1, 0)

    Then you could hide that Parent column so it's not visible on the sheet. You may also want to lock it, so no one can change the formula. It will automatically populate down as new rows are added.

    Would this help? Do you have more than one level of hierarchy? If this wouldn't work for you, it would be useful to see a screen capture of your sheet to see how it's set up (but please block out any sensitive data).

    Thanks!

    Genevieve

  • Stefan
    Stefan ✭✭✭✭✭✭

    Hi Richard,

    "PARENT / CHILDREN functions auto-populate when new rows are added below a PARENT" - yes, they do. Your problem seems to be people missing to indent a new row to the correct level, right? Sounds more like human factor to me.

    Depending on where new rows go you may either train them to mind the indentation, place a visual reminder on the sheet or similar, or you can use a form to capture new rows if they all go to the bottom or top of your sheet.

    What I think about is this " returning figures that are lower than that displayed on a sheet"... If you get the same figures calculated ok in the sheet but not in "downstream" (via summary sheets?) reports, maybe something is wrong with the calculation downstream?

    Just my guessing without knowing more about your setup.

    Hope this helps

    Stefan

    Smartsheet Consulting, Solution Building, Training and Support.

    Projects for Processes and for People.

  • Richard Heath
    Richard Heath ✭✭✭✭✭✭

    Thanks to both of you for your feedback. By providing an example it might be a little clearer.

    We run a sheet with multiple PARENT rows summing CHILDREN. This might be a register where our users' over the course of a 12 month project continually add additional rows below each of the PARENT rows. Typically, the template has two CHILDREN per PARENT allowing our users to add additional CHILDREN as required.

    I have just tested the process and what I've found is that if a user inserts a number of blank rows,which our users do, the =SUM(CHILDREN()) formula doesn't carry over if data is NOT entered in a continual fashion, top to bottom. (Only $15 caluclated, should be $20 for example)

    It would appear that a user needs to include a continous column of data, for the formula to automatically apply the connection between PARENTS and CHILDREN.

    So going back to my origianl post, a Dashboard would report only $15 down-stream, when in fact our user at input $20 under the Parent Row (First Image).

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!