Getting top-level heading's rank

12/28/21
Answered - Pending Review

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([email protected]), 3)), IF(ISERROR(INDEX(ANCESTORS([email protected]), 2)), IF(ISERROR(INDEX(ANCESTORS([email protected]), 1)), "Workstream", "Epic"), "Story"), "Task")

And another column that shows each row's top-level heading:

=IF(COUNT(ANCESTORS([email protected])) > 0, INDEX(ANCESTORS([email protected]), 1), [email protected])

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([email protected], JOIN(COLLECT(Primary:Primary, Level:Level, "Workstream")))

Then applying a RANKEQ function to that column:

=RANKEQ([email protected], 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

Answers

  • 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([email protected], Top:Top, 0)

    But they both achieve the same end-goal!

    Cheers,

    Genevieve

Sign In or Register to comment.