Determining an identifier based on parent/children and an additional factor
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!