Getting top-level heading's rank

bawbgale

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

Genevieve P.

Hi @bawbgale

Your set-up is what I would do, and then hide the two supporting columns. I'm not sure there is a way to do this in one column - you need the helper column in order to create numerical values to reference in your RANKEQ formula.

The only difference I would personally make is to use a MATCH formula instead of a FIND formula in your TopRankHelper:

=MATCH(Top@row, Top:Top, 0)

But they both achieve the same end-goal!

Cheers,

Genevieve