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

SLatchford
SLatchford ✭✭
edited 12/09/19 in Formulas and Functions

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

 

ss HELP.PNG

Comments

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!