Formula TO NOT have to manully enter formula for every row

BESP10

Good morning, 

Looking for some help on this issue. 

We have a sheet with hundreds of rows that tally up to a total (1049.86 hours per below JPEG)

We need to calculate the % each piece is from the total . These are the children rows.

The first formula I got to work by using =SUM([Total Hours Per Peice]42 / ([Total Hours Per Peice]41))

How do I get all the children rows to reference the parent row without having to manually change the formula in every row?

 

Thank you 

L_123

2 ways.

 

=[total hours per peice]42 / [total hours per peice]$41

=[total hours per peice]42/ parent([total hours per peice]42)

BESP10

thank you

 

does the $ sign mean that it will always reference that cell?

BESP10

Sorry , one more question, how do I get it to work so that all the children rows calculate if a new row is created? 

 

thanks

 

 

L_123

it means it will always reference that row. if you want to always reference that specific cell you need 2 dollar signs

 

 

row and column

$[Primary Column]$1

row

[Primary Column]$1

Column

$[Primary Column]1

Nothing

[Primary Column]1

 

L_123

Depends. How are you adding the new rows?

BESP10

when we have an add to the job (more scope) we manually enter a new  at the bottom of the sheet, but we are going to also add the ability to do it by a form as well

 

thanks

 

 

 

 

L_123

There is only one parent child reference or are you planning on adding to multiple?

BESP10

there is only one parent child reference

 

thanks

 

 

Paul Newcome

Keep in mind that using a form to add new rows will not pull down formulas if there is any hierarchy in the row above. In other words... If the last row is a child row, the next row entered by a form will not automatically be a child row and will not automatically pull the formula.

L_123

I would get rid of the parent child reference and just use column references instead. It is a lot more straightforward. If you are worried about collapsing your sheet you can build some shared filters to get a similar functionality. a column reference is made by simply deleting the row reference off a range

 

Ex

[Column Name]:[Column Name]