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
-
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!
Smartsheet Consulting, Solution Building, Training and Support.
Projects for Processes and for People.
Answers
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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.
-
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).
-
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!
Smartsheet Consulting, Solution Building, Training and Support.
Projects for Processes and for People.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!