Child Row Formula to Reference Parent Row

mhint829

Hello! I'm trying to figure out a formula that will always reference the Parent Row. I need all the cells in the Practice Name column to reference the parent row above it.

I'm assuming I can't make this a column formula, otherwise the parent row I need to reference will be referencing the parent row above that. So for that, I probably need a helper column?

If you can help with my initial question that would be great, and if you have input on column formula, let me know. I'm trying to avoid having to enter formulas when new rows are added, but just the initial parent row reference formula will be helpful. Thank you!!

Sarah Keortge

Thanks!

OK, so this should be easy to do by adding that helper column. You can name it something like [Level] with the following column formula:

=COUNT(ANCESTORS()) + 1

Then, use this as your column formula in the Parent Name column:

=IF(Level@row = 1, "", IF(Level@row = 2, [Task Name]@row, PARENT()))

Which will leave the cell blank if it's at that top level, it will return the Task Name if it's the next level down, and then any children of that will inherit the parent value.

Technically, you could embed the level piece within the formula but I think you'll find additional utility with a separate Level column (i.e., conditional formatting!)

Sarah Keortge

Returning the parent for every row is simply =PARENT() in the Practice Name column. I don't think that's what you're actually asking for, though, so could you please mock up a sample of what you would want the end result to look like and include the collapsed rows? Also, do you have a helper column with hierarchy/level data for each row? You may be able to create a conditional column formula in the Practice Name column but the community will need a clearer picture of what you want to end up with in order to provide you with the appropriate solution.

mhint829

Hi Sarah,

End result should be that all cells underneath the rows marked with red below are populating the parent row (the row marked with red). I haven't created a helper column yet but I am thinking I'll need it if I want this to be automatic, including when new rows are inserted.

Sarah Keortge

Thanks!

OK, so this should be easy to do by adding that helper column. You can name it something like [Level] with the following column formula:

=COUNT(ANCESTORS()) + 1

Then, use this as your column formula in the Parent Name column:

=IF(Level@row = 1, "", IF(Level@row = 2, [Task Name]@row, PARENT()))

Which will leave the cell blank if it's at that top level, it will return the Task Name if it's the next level down, and then any children of that will inherit the parent value.

Technically, you could embed the level piece within the formula but I think you'll find additional utility with a separate Level column (i.e., conditional formatting!)

mhint829

Thanks, Sarah! 😊