Identifying which rows are CHILDREN

Richard Heath

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.

Stefan

Hi Richard,

ok, I still think it's a handling error. I did a test with 2 columns only.

Column 1 = "Other" type primary column + some characters in every row

Column 2 = "Numbers" type text/numbers with some numbers + in the top row the sum children function

Step 1: I indented some rows, created 3 indented sections and sum children worked as expected.

Step 2: I added, deleted and moved some rows, no problem.

Step 3: I deleted all characters of an indented section in column 1 and voila sum children did no longer work... because the indentation disappeared.

Step 4: I added the indentation for the same section without adding any character to column 1 and sum children worked again.

That's why I think it's more a case of "interesting" handling of data in your sheet ;-) In my experience, sum children is a very robust function.

Hope this helps to shed light into what's actually happening.

Stefan

PS: Great example for setting up visual reminders with comfort from Genevieve!

Genevieve P.

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

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

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).

Stefan

Hi Richard,

ok, I still think it's a handling error. I did a test with 2 columns only.

Column 1 = "Other" type primary column + some characters in every row

Column 2 = "Numbers" type text/numbers with some numbers + in the top row the sum children function

Step 1: I indented some rows, created 3 indented sections and sum children worked as expected.

Step 2: I added, deleted and moved some rows, no problem.

Step 3: I deleted all characters of an indented section in column 1 and voila sum children did no longer work... because the indentation disappeared.

Step 4: I added the indentation for the same section without adding any character to column 1 and sum children worked again.

That's why I think it's more a case of "interesting" handling of data in your sheet ;-) In my experience, sum children is a very robust function.

Hope this helps to shed light into what's actually happening.

Stefan

PS: Great example for setting up visual reminders with comfort from Genevieve!