Using Index with Collect to Copy information from one sheet to another sheet for an entire column

Kique97 · 11/24/21

Hello,

I have 2 existing SmartSheets:

Sheet 1: A Master sheet with 2 columns:

Sheet 2: Another Sheet where only the "Die Name" where "Is Die 1 New?" = "true" is captured.

I have tried the following formula under row 1 under "Unique Die Name" in the 2nd sheet: =INDEX(COLLECT({Die Name}, {Is Die 1 New?}, "true"), 1)

This seems to work fine with the 1st row entry on my 2nd sheet. The problem is, when I click and drag the formula onto the rows below the 1st row, it appears as if the formula does not change at all.

The goal: I would like sheet #2 to display, for each row in sheet #1 where "Is Die 1 new?" = true, the corresponding die name (A,B, and C).

I don't know why I am only getting "A" for all rows in the new sheet. It would make more sense if I saw a few errors rather than the same value repeating.

Any help would be much appreciated.

Genevieve P. · 11/25/21

Hi @Kique97

The reason you're getting the same result is because of the 1 at the end of the INDEX function. This is finding the 1st matching value.

To bring back "b", you could swap it out in the second row to be 2:

=INDEX(COLLECT({Die Name}, {Is Die 1 New?}, "true"), 2)

And then for "c":

=INDEX(COLLECT({Die Name}, {Is Die 1 New?}, "true"), 3)

An alternative would be to bring back all of the matching values into one cell, using JOIN(COLLECT, like so:

=JOIN(COLLECT({Die Name}, {Is Die 1 New?}, "true"), " / ")

Cheers,

Genevieve

Using Index with Collect to Copy information from one sheet to another sheet for an entire column

Answers

Help Article Resources

Categories