Child Row Formula to Reference Parent Row
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!!
Best Answer

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

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.

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.

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

Thanks, Sarah! 😊
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.8K Get Help
 406 Global Discussions
 218 Industry Talk
 457 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 297 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!