Assigning a Level Based on CHILD items

David Noël

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!

Matt Lynn ACT

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 ACT

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

David Noël

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

Thank you both so very much!!

Matt Lynn ACT

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

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

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

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

Matt Lynn ACT

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 ACT

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

David Noël

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

Thank you both so very much!!