Reflecting only Parent Rows (multiple) on Dashboard

For a project release, we're leveraging checkboxes to track completion of validation tasks. I have a dashboard built to reflect progress, however am having trouble finding a way to only choose the multiple Parent Rows/Roll Up data, as it's making me include child data or nothing.. (including all the child row stats will be too much info for the audience). Any suggestions?

Find more posts tagged with

Formulas

Reports

Importing and Exporting

Comments

Andrée Starå

Hi Rick,

The easiest way is to create another column in the sheet(s) with checkboxes and then check the ones that you want to show in the Dashboard. Also, change the criteria in the report.

Would that work for you?

I hope this helps you!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Paul Newcome

TO increase flexibility and to expand on Andree's suggestion, use the formula

=IF(COUNT(CHILDREN()) > 0, 1)

What this does is count the children and check the box if there is 1 or more (meaning it is a parent row).

You can drag fill this down and it will automatically populate in new rows. That will avoid having to do it manually.

I personally use the following (in a text type column)

=IF(COUNT(ANCESTORS([Column Name]1)) < COUNT(ANCESTORS([Column Name]2)), COUNT(ANCESTORS([Column Name]1)))

This will give you a numeric value of which level of hierarchy your parent row is. The lower the number, the higher the level. The highest level being 0. From there you can sort, filter, pull reports based on ANY level of hierarchy.

(You can now also use Conditional Formatting to apply different colors, etc. to different levels without having to do those manually as well... )

Rick_Bailey

Thank you both!

Andrée Starå

Happy to help!

Paul Newcome

No worries.

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…