Getting top-level heading's rank
I'm trying to write a formula that extracts the position of the current hierarchy's top-level heading within the set of all top-level headings. The purpose is to drive some conditional formatting. i.e. If the current row is under the first top-level heading, make it green. If it's under the second top-level heading, make it blue, etc. I want to use this as a template, so I'm trying to avoid encoding the actual heading values into the formatting condition.
I was able to solve it with some helper columns but I'm wondering if anyone can come up with a more elegant solution.
My template already has a Level column that marks the row as a Workstream, Epic, Story or Task based on its level in the hierarchy:
=IF(ISERROR(INDEX(ANCESTORS(Primary@row), 3)), IF(ISERROR(INDEX(ANCESTORS(Primary@row), 2)), IF(ISERROR(INDEX(ANCESTORS(Primary@row), 1)), "Workstream", "Epic"), "Story"), "Task")
And another column that shows each row's top-level heading:
=IF(COUNT(ANCESTORS(Primary@row)) > 0, INDEX(ANCESTORS(Primary@row), 1), Primary@row)
So my goal is to add another column that returns 1 for every row under the first top-level heading, 2 for every row under the next, etc. I can get those headings with the COLLECT function, but there seems to be no direct way to get the position of an item in the collection. So I'm taking a roundabout path of JOINing them into a string and FINDing the start position of each heading in the TopRankHelper column:
=FIND(Top@row, JOIN(COLLECT(Primary:Primary, Level:Level, "Workstream")))
Then applying a RANKEQ function to that column:
=RANKEQ(TopRankHelper@row, DISTINCT(TopRankHelper:TopRankHelper), 1)
Here's a test sheet if anyone can see a way to do this in a single formula: https://app.smartsheet.com/b/publish?EQBCT=3fafe34432554d66a4da2ceb582e64a1
Help Article Resources
Check out the Formula Handbook template!