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 Non-Parent
=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 "non-parent" 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
Business Process Analyst 3 | C5ISR Group
HII | Mission Technologies
EAP | Mobilizer | Automagician | Superstar | Community Champion
Original Smartsheet Profile: @Sherry Fox
-
@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.
"Even my contingencies have contingencies."
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 429 Global Discussions
- 149 Industry Talk
- 488 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 153 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!