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


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.
    Genevieve P. Employee Admin

    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"), " / ")



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!