INDEX COLLECT line number

Julie Barbeau

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. 

Julie Barbeau

Thanks @L@123 it works :D

MCorbin

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

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

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

Thanks @L@123 it works :D