RANKEQ with INDEX / COLLECT Across Multiple Sheets

estradamee

Hello Smartsheet Community,

I have a need to INDEX / COLLECT dates from multiple different sheets with individual users. The goal is to look at various schedules for a list of users and deconflict dates that overlap on all of the schedules that a user is found. I can't do this with a report, as the assigned user is a multi-select drop down, which can't be grouped by individual user. Wouldn't that be easy…

It's possible I made it more complicated than it needs to be, but essentially I made a helper sheet that sums the counts of instances that a user's name appears in each schedule, then I apply an auto number column which explodes that count by user and applies a RANKEQ them. Next step is to INDEX / COLLECT the dates with the row index of the RANKEQ to pull the first date where the user's name appears in the schedule, and so forth. Works fine until the first schedule reaches it's max index.

Basically RANKEQ does not reset when the schedule has reached it's limit and moves to the next schedule to look for corresponding INDEX / COLLECT name and date referencing the rank. Hopefully this illustrates my issue:

User A - 20 assignments across all schedules.

Schedule 1 has 10 instances of User A, RANKEQ 1-10

Schedule 2 has 10 instances of User A, RANKEQ 11-20

The INDEX / COLLECT index, set to reference the RANKEQ for Schedule 2 begins at 11, so it results in an invalid value as there is no row index greater than 10. I need the RANKEQ to reset back to 1 when the schedule has reached it's max index, but this results in a circular reference since the rank isn't able to look at any count or reference from the cross sheet count.

I feel like I almost have the answer, but a fresh set of eyes or ideas would be very helpful. If anyone has the time, have at it!

jmyzk_cloudsmart_jp

Hi @estradamee

This is a classic challenge when working with multi-select Users across multiple sheets.
Since Smartsheet doesn’t natively “explode” multi-select values, combining schedules per user requires some creative structuring.

Demo Source Schedule Sheets

https://app.smartsheet.com/b/publish?EQBCT=8b70d155ccb842ae8d62e1529a650187

https://app.smartsheet.com/b/publish?EQBCT=c8b5bc5864ff4e6782c416f2ac849bc2

I’ve used two approaches depending on the use case:

Overview of Two Methods

Method 1 — Per User Sheet

https://app.smartsheet.com/b/publish?EQBCT=38c3395fe97c4a58b9405274b2d67d42

https://app.smartsheet.com/b/publish?EQBCT=98bd7e0c67234aa79142476a8dd4e33d

• Create one sheet per user
• Pull that user’s dates from each schedule using INDEX/COLLECT
• No global ranking needed

Best when access control (who sees what) is important

Method 2 — Single Sheet with Helper (Cumulative Model)

https://app.smartsheet.com/b/publish?EQBCT=469a460930b64b8a92f422a4276ebbfb

https://app.smartsheet.com/b/publish?EQBCT=80ac9cfb62ea4578afe437363a64eab9

• Use a helper sheet to count schedules per user
• Expand users into rows using cumulative totals
• Use scoped RANKEQ to get the local index

Best when a centralized view and scalability are important

Method 1 — Per User Sheet (Detailed)

Concept

Each user gets their own sheet.
A simple row index ([#]) is used to sequentially pull dates from each schedule.

Instead of a global ranking, we:

• Count occurrences per schedule
• Use index offsets to switch between schedules

Sheet Summary

[Schedule 1 Count]# = COUNTIF({Schedule 1 : User}, HAS(@cell, User#))

[Schedule 2 Count]# = COUNTIF({Schedule 2 : User}, HAS(@cell, User#))

Main Logic

Date

=IF(

    [#]@row <= [Schedule 1 Count]#,

    INDEX(

        COLLECT({Schedule 1 : Date}, {Schedule 1 : User}, HAS(@cell, User#)),

        [#]@row

    ),

    IF(

        [#]@row <= [Schedule 1 Count]# + [Schedule 2 Count]#,

        INDEX(

            COLLECT({Schedule 2 : Date}, {Schedule 2 : User}, HAS(@cell, User#)),
 [#]@row - [Schedule 1 Count]#

        )

    )

)

Key idea:

[#] - [Schedule 1 Count]#

This resets the index for Schedule 2.

Source Sheet

=IF([#]@row <= [Schedule 1 Count]#, 1,

   IF([#]@row <= [Schedule 1 Count]# + [Schedule 2 Count]#, 2))

Overlap

=AND(ISDATE(Date@row), COUNTIF(Date:Date, Date@row) > 1)

Why it works

• No RANKEQ, so no reset issue
• Each schedule is handled independently
• Fully compatible with HAS() for multi-select

Pros

• Each user only sees their own data
• Strong access control
• Easy to share per user

Cons

• Requires multiple sheets
• Maintenance increases as users grow
• Needs a report to combine all users

Best for

• Privacy-sensitive scenarios
• When users should not see other users’ schedules

Method 2 — Single Sheet (Cumulative Model)

Concept

Instead of splitting by user, we:

1. Count user occurrences per schedule
2. Build a cumulative total
3. Assign rows to users based on cumulative ranges
4. Use scoped RANKEQ to generate a local index

Helper Sheet

[Schedule 1] = COUNTIF({Schedule 1 : User}, HAS(@cell, User@row))

[Schedule 2] = COUNTIF({Schedule 2 : User}, HAS(@cell, User@row))

[Total]      = [Schedule 1]@row + [Schedule 2]@row

[Cumulative] = SUMIFS(Total:Total, [Row ID]:[Row ID], <=[Row ID]@row)

Main Logic

Step 1 — Assign User

=IF([#]@row <= INDEX({User Schedule Count : Cumulative}, 1),

    INDEX({User Schedule Count : User}, 1),

IF([#]@row <= INDEX({User Schedule Count : Cumulative}, 2),

    INDEX({User Schedule Count : User}, 2),

IF([#]@row <= INDEX({User Schedule Count : Cumulative}, 3),

    INDEX({User Schedule Count : User}, 3),

IF([#]@row <= INDEX({User Schedule Count : Cumulative}, 4),

    INDEX({User Schedule Count : User}, 4)))))

This expands users into rows.

Step 2 — Source Sheet

=IF(

    RANKEQ([#]@row, COLLECT([#]:[#], User:User, User@row), 1)

    <= INDEX({User Schedule Count : Schedule 1},

             MATCH(User@row, {User Schedule Count : User}, 0)),

    1,

    2

)

Step 3 — Local Index

=RANKEQ(

    [#]@row,

    COLLECT(

        [#]:[#],

        User:User, User@row,

        [Source Sheet]:[Source Sheet], [Source Sheet]@row

    ),

    1

)

RANKEQ is scoped by User and Source Sheet, so it resets correctly.

Step 4 — Date

=IF([Source Sheet]@row = 1,

    INDEX({Schedule 1 : Date}, Index@row),

IF([Source Sheet]@row = 2,

    INDEX({Schedule 2 : Date}, Index@row)))

Step 5 — Overlap

=AND(

    ISDATE(Date@row),

    COUNTIFS(Date:Date, Date@row, User:User, User@row) > 1

)

Why it works

• Cumulative logic replaces global ranking
• Scoped RANKEQ avoids reset issues
• Everything is handled in one sheet

Pros

• Single sheet structure
• Easier reporting and dashboards
• Scales better with more users

Cons

• If shared, users can see other users’ schedules and overlaps
• More complex formulas
• Harder to debug

Best for

• Team scheduling
• Resource planning
• Situations where visibility across users is needed

Final Thought

Both methods solve the same technical limitation, but the choice depends on visibility requirements:

• Use Method 1 if users should only see their own schedule
• Use Method 2 if users should see the full team view

The key decision is whether users should have isolated or shared visibility.

Hope this helps. Curious how others are solving multi-select scenarios as well.