Formula TO NOT have to manully enter formula for every row

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

Find more posts tagged with

Workspaces

Sheets

Formulas

Comments

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

[Column Name]:[Column Name]

Sign in to join the conversation!

Quick Links

Trending discussions

Conditional Formatting Query
All the above rules are working except the 4th one in the list which asks for the cell to turn red if Group status is not started and end date is in next 14 days. I have used the Smartsheet AI tool but no luck, I have saved and refreshed. I am using a helper column for my 5th rule in the list which asks the cell to turn…
Dropdown list restrictions
Is there a way to limit a dropdown list so that the values in the dropdown list can only be used once in a column?
Pull Value of Cell from other Sheet IF
I have tried this a variety of ways. I have Hotel and then 3 standard Vendor Types. I am checking to see if certain information has been entered for each of these on Sheet B. I have the formula pulling the information for these correctly. The issue is with any non-standard vendor types. Since not all meetings will have an…