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

Andy Lorance

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!!

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

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

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?