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!

Find more posts tagged with

Formulas

Accepted answers

Kelly Moore

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

All comments

Kelly Moore

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

Kaitlyn Carroll

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?

Kelly Moore

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

Kaitlyn Carroll

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

sbrelage

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?

Sherry Fox

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

Bill in Ohio

@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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.