INDEX COLLECT line number

Hi guys!

I have problem with INDEX/COLLECT. I want the line index to be equivalent to the line number. I tried @row but it doesn't work. If I manually set the number ''1'', when I drag my formula down, the number ''1'' doesn't change for the next line. 



Best Answer

Answers

  • MCorbin
    MCorbin Overachievers Alumni

    What are you trying to accomplish? I'm not sure that Index and Collect would play well together. Collect is designed to give multiple values, Index is expected to bring back a single value. Can you give more information on what you're trying to calculate?

  • Julie Barbeau
    Julie Barbeau ✭✭✭
    edited 11/16/20

    Hello, 


    I have, in a second sheet, a list of projects with columns to be checked off as needed (see attached). What I'm trying to do with my INDEX formula is to go get only the projects that have column D-A checked and copy them into my first sheet. 


  • L_123
    L_123 ✭✭✭✭✭✭
    edited 11/16/20

    Index(collect()) is a great formula, and one I use very often. The trick to it is you need 2 formulas. 1 for the first cell, the second for the rest.


    First cell:

    =index(collect(),1)

    Second Cell:

    =index(collect(),1+count(FirstCell$1:FirstCell1))

    Then drag down. Usually i'll wrap the second in an iferror() so I can drag down as far as I want for future potential values.

    =iferror(index(collect(),1+count(FirstCell$1:FirstCell1)),"")

  • Julie Barbeau
    Julie Barbeau ✭✭✭
    Answer ✓

    Thanks @L@123 it works :D

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!