Assigning a Level Based on CHILD items

David Noël
David Noël ✭✭✭✭
edited 07/30/24 in Formulas and Functions

I have 4 different PARENTs with many CHILD items under each of the 4 PARENTS. The PARENT column is named "Item / Topic". I want to assign Level 1 for the first PARENT, Level 2 for the second PARENT and so on. The first PARENT is RISKS. This formula worked and assigned a value of 1. Also, if there is a CHILD items that reads "<entries here>", I do not want that to have any value.

=IF(COUNT(CHILDREN()), "RISKS" <> "<entries here>", 1)

I then tried a different formula thinking I would need more logic to begin looking at the other PARENTs.

=IF(OR(COUNT(ANCESTORS([Item / Topic]@row)) = "RISKS", COUNT(CHILDREN([Item / Topic]@row)) > 0), 1)

The second PARENT is ASSUMPTIONS / ACTIONS - DECISION. I reworked the formula to see if I could get a value of 2.

=IF(OR(COUNT(ANCESTORS([Item / Topic]@row)) = "RISKS", COUNT(CHILDREN([Item / Topic]@row)) > 0), 1, IF(COUNT(ANCESTORS([Item / Topic]@row)) = "ASSUMPTIONS / ACTIONS - DECISIONS", COUNT(CHILDREN([Item / Topic]@row)) > 0), 2)\

I am now getting an #INCORRECT ARGUMENT SET.

The desired result would be for any children under RISKS to have a value of 1 (less the <entries here> row); any children under ASSUMPTIONS / ACTIONS - DECISIONS to have a value of 2 (less the <entries here> row) and then 3 for ISSUES / IMPACTS (CUSTOMER & OPERATIONAL) and 4 for DEPENDENCIES - CHANGES.

As you can see, I am kind of all over the place. Any insight would be greatly appreciated!

Best Answers

  • Matt Lynn#
    Matt Lynn# Community Champion
    Answer ✓

    Paul's version works for me… or this revised of my original.

    =IF([Item / Topic]@row = "<Entries Here>", "", IF([Item / Topic]@row = "Risks", 1, IF([Item / Topic]@row = "Assumptions / Actions - Decisions", 2, IF([Item / Topic]@row = "Issues / Impacts (Customer & Operational)", 3, IF([Item / Topic]@row = "Dependencies - Changes", 4, IF(INDEX(ANCESTORS([Item / Topic]@row), 1) = "Risks", 1, IF(INDEX(ANCESTORS([Item / Topic]@row), 1) = "Assumptions / Actions - Decisions", 2, IF(INDEX(ANCESTORS([Item / Topic]@row), 1) = "Issues / Impacts (Customer & Operational)", 3, IF(INDEX(ANCESTORS([Item / Topic]@row), 1) = "Dependencies - Changes", 4)))))))))

    Matt Lynn

  • Matt Lynn#
    Matt Lynn# Community Champion
    Answer ✓

    If you're still getting an error double check the spelling/characters within my quotes. It's possible one of us missed a character.

    Matt Lynn

  • David Noël
    David Noël ✭✭✭✭
    Answer ✓

    It would help if I was spelling Dependencies correctly for that PARENT…. geez!

    Thank you both so very much!!

Answers

  • Matt Lynn#
    Matt Lynn# Community Champion

    @David Noël Try this as a column formula… it will work for children, grandchildren etc.

    =IF([Item / Topic]@row = "Risks", 1, IF([Item / Topic]@row = "Assumptions / Actions - Decisions", 2, IF([Item / Topic]@row = "Issues / Impacts (Customer & Operational)", 3, IF([Item / Topic]@row = "Dependencies - Changes", 4, IF(INDEX(ANCESTORS([Item / Topic]@row), 1) = "Risks", 1, IF(INDEX(ANCESTORS([Item / Topic]@row), 1) = "Assumptions / Actions - Decisions", 2, IF(INDEX(ANCESTORS([Item / Topic]@row), 1) = "Issues / Impacts (Customer & Operational)", 3, IF(INDEX(ANCESTORS([Item / Topic]@row), 1) = "Dependencies - Changes", 4))))))))

    Matt Lynn

  • David Noël
    David Noël ✭✭✭✭

    I am getting an #INVALID VALUE. Thoughts? Also, if the line reads "<entries here>", I do not want the Level column to display a value.

  • Paul Newcome
    Paul Newcome Community Champion

    Try this:

    =IF([Item / Topic]@row <> "<entries here>", IF(IFERROR(INDEX(ANCESTORS([Item / Topic]@row), 1), [Item / Topic]@row) = "RISKS", 1, IF(IFERROR(INDEX(ANCESTORS([Item / Topic]@row), 1), [Item / Topic]@row) = "ASSUMPTIONS / ACTIONS - DECISIONS", 2, IF(IFERROR(INDEX(ANCESTORS([Item / Topic]@row), 1), [Item / Topic]@row) = "ISSUES / IMPACTS (CUSTOMER & OPERATIONAL)", 3, 4))))

  • David Noël
    David Noël ✭✭✭✭

    VERY close. I am getting a value of 4 for ASSUMPTIONS and that should be 2. Any thoughts?

  • Matt Lynn#
    Matt Lynn# Community Champion
    Answer ✓

    Paul's version works for me… or this revised of my original.

    =IF([Item / Topic]@row = "<Entries Here>", "", IF([Item / Topic]@row = "Risks", 1, IF([Item / Topic]@row = "Assumptions / Actions - Decisions", 2, IF([Item / Topic]@row = "Issues / Impacts (Customer & Operational)", 3, IF([Item / Topic]@row = "Dependencies - Changes", 4, IF(INDEX(ANCESTORS([Item / Topic]@row), 1) = "Risks", 1, IF(INDEX(ANCESTORS([Item / Topic]@row), 1) = "Assumptions / Actions - Decisions", 2, IF(INDEX(ANCESTORS([Item / Topic]@row), 1) = "Issues / Impacts (Customer & Operational)", 3, IF(INDEX(ANCESTORS([Item / Topic]@row), 1) = "Dependencies - Changes", 4)))))))))

    Matt Lynn

  • Matt Lynn#
    Matt Lynn# Community Champion
    Answer ✓

    If you're still getting an error double check the spelling/characters within my quotes. It's possible one of us missed a character.

    Matt Lynn

  • David Noël
    David Noël ✭✭✭✭
    Answer ✓

    It would help if I was spelling Dependencies correctly for that PARENT…. geez!

    Thank you both so very much!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!