Formula regarding parent / child rows
Hello,
I'm trying to create a formula to automatically populate a column based on if the row's primary column cell is a "parent" or "child."
The column I want to automate is titled "Level." If the primary column cell at that row is a "parent", I want the "Level" column cell at that row to automatically populate "1." If it's a "child", I want it to automatically populate "2."
Is there a formula for this?
Thanks!
Best Answer

Hey @Kaitlyn Carroll
Sorry, I missed you had replied. Try this
=IF(OR(COUNT(ANCESTORS([your primary column]@row)) = 0, COUNT(CHILDREN([your primary column]@row)) > 0), 1, IF(COUNT(ANCESTORS([your primary column]@row)) > 0, 2))
Insert the real name of your primary column if the open parentheses give you a circular error
Does this work for you
Kelly
Answers

Hey @Kaitlyn Carroll
This formula will indicate if a row is a Parent or NonParent
=IF(COUNT(CHILDREN())>0, 1, 2)
If your sheet also has rows that are neither Parents or Children, it will also indicate these rows as a 2
If this is a concern, you must add an additional condition
=IF(AND(COUNT(ANCESTORS())=0, COUNT(CHILDREN())>0), 1, IF(AND(COUNT(ANCESTORS())>0, COUNT(CHILDREN())=0), 2))
Will either of these work for you?
Kelly

Hi Kelly,
Thank you for the formulas!
The first formula would be great. However, as you stated, if it's neither a Parent or Child it's marked as "2" and that won't work.
When I use the second formula you shared, it gives me the following errors:
Any ideas? Also, I noticed the cell is blank when it's not a "Parent." I would need those "nonparent" rows to be "1" still. Is that possible?

Hey @Kaitlyn Carroll
Sorry, I missed you had replied. Try this
=IF(OR(COUNT(ANCESTORS([your primary column]@row)) = 0, COUNT(CHILDREN([your primary column]@row)) > 0), 1, IF(COUNT(ANCESTORS([your primary column]@row)) > 0, 2))
Insert the real name of your primary column if the open parentheses give you a circular error
Does this work for you
Kelly

That worked! I'm so excited!! Thank you so much!

So I would like to use this formula to provide info at 4 levels of the hierachy, see example below. I've messed with this formula for the last 30 minutes and now have completely confused myself. Any ideas?

I have a need for the same exact formula type with multiple level of hierarchy
Sherry Fox
Data Science & Reporting Specialist  PA Performance & Data Insights
UnitedHealth Group  OptumRx
EAP  Mobilizer  Automagician  Superstar  Community Champion
https://www.linkedin.com/in/sherryfox/

@Sherry Fox , this has worked for my needs:
=IF(COUNT(ANCESTORS([PRIMARY]@row)) > 1, COUNT(ANCESTORS([PRIMARY]@row)), IF(COUNT(ANCESTORS([PRIMARY]@row)) = 1, 1, 0))
Change the [PRIMARY] to your primary column. Let me know if this works for you. It's not a complex formula, but I was just looking for calculating the top level to zero, and then primary items to be level 1, secondary as 2, and continuing.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.6K Get Help
 403 Global Discussions
 215 Industry Talk
 454 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 56 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 296 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!