Progress with children rows

Netanel yosef

Hello all

I have a tracking sheet for payments under several Purchase Orders (POs). Each PO can have different numbers of milestone payments—anywhere from one to ten or more—until the PO is completely paid.

For each milestone, I need to mark it as 100% and then i need send invoice. The parent row should then show the percentage billed from the total PO value. For example, if the PO value is $1,000,000 and a partial payment of $250,000 is made, the progress should be shown as 25%.

The problem is that I constantly need to copy and paste the formula in the parent row to update the progress. Is there a way to update the formula automatically, or any other ideas for tracking this?

here's link to the sheet

https://app.smartsheet.com/b/publish?EQBCT=a4b20d1ac6024990ad4a54140a90b77b

Protonsponge

Hello @Netanel yosef

I'm not sure if this would work for you or not, but you could convert your [Progress %] column to a column formula with the formula below:-

=IFERROR(IF(COUNT(CHILDREN()) > 0, AVG(CHILDREN()), [Progress Value]@row / [Approved Work Value]@row), " ")

The formula is asking if the count of children is greater than 0, (e.g. has child rows), then average the child rows, else divide [Progress Value] against [Approved Work Value] for each row. The IFERROR is there to accommodate for any blank row.

This would be a change in the way you currently use the sheet in that rather than entering 100 % in [Progress %], you would be adding the amount paid in [Progress value]

I hope that is helpful to you in some way,

Protonspounge