# Formula regarding parent / child rows

Options

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!

Tags:

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
edited 02/11/23
Options

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

• Options

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?

• ✭✭✭✭✭✭
Options

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

• Options

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

• Options

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?

• ✭✭✭✭✭✭
Options

I have a need for the same exact formula type with multiple level of hierarchy

Sherry Fox

Del-Air Heating, Air Conditioning, Plumbing and Electrical

EAP | Mobilizer | Automagician | Superstar | Community Champion

• ✭✭✭
Options

@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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!