How can I use index-collect across two different sheets?

Andrew Ryback

Hi everyone,

I'm looking to create an index-collection formula that pulls data from two separate sheets, but I'm running into some issues.

I have a 2024 calendar that lists confirmed projects, and the producer assigned to each project.

I've also built a 2025 calendar with all the same data.

I'm building a separate sheet and looking to pull the producer name assigned to each project in 2024 and 2025. Below is the formula I've entered, which is spitting back "incorrect argument".

Any thoughts on what I'm doing wrong?

=INDEX(COLLECT({PRODUCER 2024}, {PROJECT 2024}, PROJECT@row, {Producer 2025}, {Project 2025}, PROJECT@row), 1)

Melissa Yamada

Hello @Andrew Ryback ,

I'd be happy to help!

When creating an INDEX/COLLECT formula with 2 separate sheets you need to create 2 INDEX/COLLECT formula and add the results.

For example:
=INDEX(COLLECT({PRODUCER 2024}, {PROJECT 2024}, PROJECT@row), 1) + INDEX(COLLECT({Producer 2025}, {Project 2025}, PROJECT@row), 1)

Andrew Ryback

Thanks, Melissa.

Here's what I have:

=INDEX(COLLECT({PRODUCER 2024}, {PROJECT 2024}, PROJECT@row), 1) + INDEX(COLLECT({Producer 2025}, {Project 2025}, PROJECT@row), 1)

…but this is still spitting back "invalid operation". Am I missing something?

Courtney S.

Maybe try troubleshooting by putting the two different INDEX/COLLECT formulas in two different columns, to see if they both work separately. You might want look into the JOIN function in combination with COLLECT:

JOIN Function | Smartsheet Learning Center

Formula combinations for cross sheet references | Smartsheet Learning Center

Melissa Yamada

Hello @Andrew Ryback

Try this:

=INDEX(COLLECT({PRODUCER 2024}, {PROJECT 2024}, PROJECT@row), 1) + " , " + INDEX(COLLECT({Producer 2025}, {Project 2025}, PROJECT@row), 1)

You can change the comma (" , ") to "and" or just space (" ").

Andrew Ryback

I've modified this as follows:

=INDEX(COLLECT({PRODUCER 2024}, {PROJECT 2024}, PROJECT@row), 1) + "," + INDEX(COLLECT({Producer 2025}, {Project 2025}, PROJECT@row), 1)

…but I am still getting "invalid operation"

I'm wondering if the project@row is somehow throwing off the formula? The project names listed in 2024 won't be the same as projects in 2025…