Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Seeking an Alternative to the Absolute Reference of a Parent Cell

John Sauber
John Sauber ✭✭✭✭✭✭
edited 12/09/19 in Archived 2015 Posts

Has anyone used a formula which can report a child's percentage of the total rolled-up in its parent without using absolute cell references? I'd like to avoid absolute references because I'd like the formula to be consistent all the way down many parent cells, which will have different absolute references to be used in the child rows.


In the screen capture below, the "Percent of Count" is calculated by dividing "Count" in the current row by the absolute reference to "Count" in the parent row (the formula for [Percent of Count]2 is =Count2 / Count$1, for example). That formula is dragged down to all the children, until the next parent, where the formula needs to be re-created with the new absolute reference of the new parent. This is just a small example using two parents, but I have this formula in mind for a real sheet with many different parents.


If a single formula can be used across all of the "families" then I can make this even more robust by adding logic which will either perform the percentage calculation, or roll-up the total as a parent, based on whether or not the row is a parent row. There are previous discussions on that logic, like the recent one , here.




This discussion has been closed.