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.

Find more posts tagged with

Accepted answers

Paul Newcome

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), "")

All comments

Paul Newcome

Are you able to provide a screenshot that has data manually entered to show what you are trying to accomplish?

Ryan Nix

Here's a quick snippet

Paul Newcome

Is the blue row row 105 in this instance? Where exactly are you putting this formula? In the white rows of the screenshot?

Ryan Nix

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.

Paul Newcome

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), "")

Ryan Nix

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!

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.