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
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.
Comments
-
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.
-
Thanks, Travis.
-
+1 on the Parent functionality
-
+ 2 on the Parent and even next level down functionality!!
Thanks!
-
+3 Please add a parrent look up function. Can the function name be whosyourdaddy()? jk All joking aside, please do add it.
Thanks
-
Mark - adding your vote for =WHOSYOURDADDY()
-
I would also like to add my vote for referencing parent!
-
Your dreams came true:
http://help.smartsheet.com/articles/775363-using-formulas#advanced
scroll a bit
-
Add my vote!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives