Seeking an Alternative to the Absolute Reference of a Parent Cell

John Sauber

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.

Travis

John, as you have seen, there isn’t a ‘parent’ function but we are tracking feedback for this and I will pass your request along to our product team. It may be possible another user has a good alternative but right now using an absolute reference would be your best option.

John Sauber

Thanks, Travis.

ACharvet

+1 on the Parent functionality

Bobko

+ 2 on the Parent and even next level down functionality!!

 

Thanks!

Mark Breneman

+3 Please add a parrent look up function. Can the function name be whosyourdaddy()? jk All joking aside, please do add it.

 

Thanks

Travis

Mark - adding your vote for =WHOSYOURDADDY() 

Jorge Castilla

I would also like to add my vote for referencing parent!

Bruno Simoes

Your dreams came true:

http://help.smartsheet.com/articles/775363-using-formulas#advanced

 

scroll a bit

Stephanie Taylor

Add my vote!