Determining an identifier based on parent/children and an additional factor

SLatchford

I have a sheet where I'm tracking IT initiatives, and some are considered Cross-Platform. Some Cross-Platform records have children and others do not. (See screenshot)

Based on some reports I need, I have to identify Cross-Platform initiatives, but do not want to include the child initiatives. I created a column called Level to indicate this. 

I'm trying to build a formula to auto-identify these Levels. The formula I've written is below, and here's what I'm trying to do: 

If it's a Cross-Platform initiative WITH children, the rollup row is Level 1 and the children should be Level 100. (all child rows can be the same Level, no incrementing); any Cross-Platform WITHOUT children is Level 2. 

=IF(PLATFORM78 = "CROSS-PLATFORM", IF((COUNT(CHILDREN(PLATFORM78))) > 0, 1, 2), 0)

I get the Level 1 and Level 2 to work, but realized I'm not accounting for the <>Cross-Platform in the child rows, so I wrote the formula below, but I know it doesn't work (I know the Level 2 will never show now). I think I need some OR formula but can't figure it out. 

=IF(PLATFORM69 = "CROSS-PLATFORM", IF((COUNT(CHILDREN(PLATFORM69))) > 0, 1, IF(PLATFORM69 <> "CROSS-PLATFORM", (COUNT(PARENT(INITIATIVE69))) > 0, 100, 2)))

Can someone help with my formula? Thanks!  

Sherri

 

Paul Newcome

If you only have the two levels (meaning no child rows having child rows of their own as well), then you could use an ANCESTORS function. Make it a checkbox column and enter

=IF(ANCESTORS@row = 0, 1, 0)

 

If the number of ancestors for that row is 0 (not a child row of anything else), it will check the box. Otherwise it won't. You could then pull your report factoring in whether or not the box is checked. You could also put that column all the way off to the right side of the sheet and "Hide" it to keep your sheet looking clean.