Assigning a Level Based on CHILD items

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
-
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
-
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
-
It would help if I was spelling Dependencies correctly for that PARENT…. geez!
Thank you both so very much!!
Answers
-
@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
-
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.
-
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))))
-
VERY close. I am getting a value of 4 for ASSUMPTIONS and that should be 2. Any thoughts?
-
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
-
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
-
It would help if I was spelling Dependencies correctly for that PARENT…. geez!
Thank you both so very much!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 487 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!