Kanban-grid to Milestone date/calendar week | find latest column with same text

IngoSchegk

Hi Everyone,

we build a pretty big sheet to track and work a bigger project. Therefore we have 3 milestones per task. One task per row, except mother rows with titles for organization. For visualisation the calendar weeks set in the 3 milestone columns were shown with colour at the right side of the sheet in a self build kanban from calendar week 1 to 52.

As of performance issues by smartsheet due to the big formulas and about 450 formatting rules for the colouring we want to try and change the working method.

Goal: Drag and drop / Copy & paste a specific name e.g. "spez" incl. manual colour in the kanban columns. In column "MS1" the calendar week of the latest (or visually the most right one) should be shown.

As we've got some phase blocker columns in between some calendar week columns I've added index to the match formula. Standing by now I can find the first column with the right naming and show the right calendar week. Formula is:

=INDEX([KW01]1:[KW30]2, 1, MATCH("spez", [KW01]@row:[KW30]@row))

Question: How can I update the formula to find the "latest column" with the right naming? For the example in the screenshot it would show then 7 as the right calendar week.

Thanks for your inputs and help in advance!

Best regards,

Ingo

IngoSchegk

Short Update: CountIf came to my mind and I found a solution that worked for me.

Formula: =INDEX([KW01]1:[KW30]2, 1, MATCH("spez", [KW01]@row:[KW30]@row)) + COUNTIF([KW01]@row:[KW20]@row, "spez") - 1

if anyone got a shorter solutions in mind that would work, feel free to share.

Thanks

IngoSchegk

Unfortunately there is still an issue. As I use the Match function in three different columns for the same range of columns it does not work properly.

Issue: First match function works properly. Second and third do not read out the column number correctly. Instead they just use 1 for the column-number with the first match. So instead of calendar week 8 it would throw out calendar week 1 if there is already some text filled in the earlier calendar week columns.

Anyone who had the same issue before and knows how to fix this issue?

Genevieve P.

Hi @IngoSchegk

If I'm understanding you correctly, you need the number in the top row to be the output of your formula, based on the max week or max number that appears where your content is placed.

So, in my image below, even though "Spez" shows twice in one row, you only want the max number (4) to appear. Is that correct?

If so, try this!

=INDEX([KW01]$1:[KW04]$1, 1, MATCH(MAX(COLLECT([KW01]$1:[KW04]$1, [KW01]@row:[KW04]@row, "Spez")), [KW01]$1:[KW04]$1, 0))

In the MATCH portion I'm using MAX(COLLECT()) to identify the maximum number based on the value "Spez".

Let me know if this works for you!

Cheers,

Genevieve