Sum values from child rows automatically with formula while allowing manual data entry into child row cells

EmilyScerriDavis

We have a project plan with resourcing allocation built in to it (hours and budget)

Currently we have a column for Allocated Hours where we are having to manually enter the below formula into the parent rows. I have figured out a work around by having a secondary column that I have hidden to sum the hours from the child rows. This is plausible as we don't necessarily need the total hours for each parent row on the plan as there are reports that pull that information onto the dashboard anyway.

=SUM(CHILDREN())

However, with the budget we do need to see both child and parent row values for each of the 3 columns (Planned Cost, Actual Cost, Cost Variance). We need to make this as seamless as possible avoiding manual entry of formulas as we have a wide range of skillset across our organisation.

I tested having a helper column with a column formula using the Allocated Hours column data (see sample of formula below), however it says #INVALID COLUMN VALUE. I understand this would be due to not being able to set the value of another cell in a formula.

=IF(Parent@row = 1, IF(COUNT(CHILDREN([Allocated Hours]@row)) = 0, "", [Allocated Hours]@row = SUM(CHILDREN([Allocated Hours]@row))), "")

Is there a different way to do this I am not thinking of?

ccarriger

What are the parent rows doing? If they're grouping by project or sections within projects, I would make that a column.

So say you're using project deliverables Widget A, Widget B, and Widget C as your parent rows. I would create a column called Project Deliverables and use my list of widgets as values. Then associate each row with the Widget value it should have; basically what it's parent row value was. Then you can run your reports and summarize with both the "child" and "parent" data visible.

You're just moving the parent row to be a usable column value.

And your formulas in that sheet may be easier because you have one column to check for a value than trying to differentiate between parent/child hierarchy.