Formula for Pivot Table Sheet - Display Parent Value in Separate Column

Greetings -

Goal: yield the parent value from a pivot table in an adjacent helper column via 'column formula' (some rows are removed / some are added which would require filling the formula down through the the added range manually if I continue to use a cell-based formula).

Current: cell-based formula. Row 1 = Category1. Row 2 and all rows thereafter = =IF(ISNUMBER(Category@row), Category@row, [Job Number]1)

Capture.PNG

Purpose of the helper column: as data is filtered, the parent row may disappear. The helper column will show the parent data regardless of the filter applied.

Thanks for any feedback/suggestions!!

Find more posts tagged with

Accepted answers

Kelly Moore

Andy,

If I have understood you correctly, you are wanting all the rows under a parent to reflect the value of the parent.

Assuming this is correct, I do this with frequently (for the exact reason you're stating) with 2 helper columns. You could combine into one, if desired, but I always have the 'Parent' helper column in all of my sheets. This allows me to do what I believe you're asking, as well as many other things.

Helper 1 = Parent Row designator. I, unimaginatively, name this column "Parent". (Once the parent row is designated, one can conditionally format, differentiate column formulas between parent and non-parent rows, etc). I like the "." rather than using a checkbox. It's a personal preference thing.

=IF(COUNT(CHILDREN([Primary Column]@row)) > 0, ".")

A parent, by definition, will have at least 1 child so the count will be greater than zero. If you have sub parents and only wanted the top row parents, check out the Hierarchy function.

Helper 2 - If I understood your question correctly, this is what you're looking for.

=IF(Parent@row = ".", [Primary Column]@row, PARENT())

The formula is true when the Parent column is a parent row. It will copy the data out of the primary column, in your case the Category data, into this helper column. If the formula is false the row is a child row. The formula will then copy whatever is in the parent row of this helper column. Don't forget to change [Primary column] to your primary column name, in this case 'Category'.

I hope this helps,

Kelly

Andy Lorance

This worked great! That was exactly what I was looking for. Thanks a million!!!!

Andy

All comments

Kelly Moore

Andy,

If I have understood you correctly, you are wanting all the rows under a parent to reflect the value of the parent.

=IF(COUNT(CHILDREN([Primary Column]@row)) > 0, ".")

A parent, by definition, will have at least 1 child so the count will be greater than zero. If you have sub parents and only wanted the top row parents, check out the Hierarchy function.

Helper 2 - If I understood your question correctly, this is what you're looking for.

=IF(Parent@row = ".", [Primary Column]@row, PARENT())

I hope this helps,

Kelly

Andy Lorance

This worked great! That was exactly what I was looking for. Thanks a million!!!!

Andy

JoeS

Does anyone know if the formula in a helper column on a pivot table will automatically populate to added rows as the pivot table is updated with new information?

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.