Formula to always reference the parent of a child row within a different column?
Hello,
I am trying using the parent/child relationship to group a set of data by month and am using the the formula to rank the children rows by their value:
=IFERROR(RANKEQ(Value@row, CHILDREN(Value$105), 0), "")
But I was wondering if there is a way to make this as a column formula? I would like it to always rank the value of the children but need to manually select the parent row.
Best Answer
-
Assuming your parent rows are unique, my suggestion would be a helper column that replicates the parent row on all of the child rows.
=IF(COUNT(CHILDREN([Primary Column]@row)) = 0, PARENT([Primary Column]@row))
Then you can work in a COLLECT function for your range.
=IFERROR(RANKEQ(Value@row, COLLECT(Value:Value, [Parent Helper]:[Parent Helper], [Parent Helper]@row), 0), "")
Answers
-
Are you able to provide a screenshot that has data manually entered to show what you are trying to accomplish?
-
Here's a quick snippet
-
Is the blue row row 105 in this instance? Where exactly are you putting this formula? In the white rows of the screenshot?
-
Yes, the blue is the parent row, similar to row 105. The white Primary Column and the Value columns are a copy and paste from a separate source. I then I have a report that will take the top 10 ranked items for each month so we can see what are all the major items within a given month.
-
Assuming your parent rows are unique, my suggestion would be a helper column that replicates the parent row on all of the child rows.
=IF(COUNT(CHILDREN([Primary Column]@row)) = 0, PARENT([Primary Column]@row))
Then you can work in a COLLECT function for your range.
=IFERROR(RANKEQ(Value@row, COLLECT(Value:Value, [Parent Helper]:[Parent Helper], [Parent Helper]@row), 0), "")
-
Looks like that might work! I'll need to tweak the parent rows a bit but I think this might do the trick. Thank you!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!