Help on Index(collect) to drag the values down

VijiNair

I have a source sheet which receives value from a DB and this sheet keeps updating on daily basis. I have added 2 new columns in the source sheet as "CreatedDate" and "Status". The "Status" value had a formula o update the value as 1 if the record is latest and 0 if the record is old. So whenever there new data is added to the source sheet , the value in "Status" column is updated to 0 or 1 accordingly.

Now I have to create a lookup in another sheet(finalsheet) to just display the records with value as 1 and this should always give real-time value.

Note: I tried copy workflow with condition, but then the finalsheet is having duplicate records because a record when has the value as 1 is updated in finalsheet. But when there is an update in the source sheet, and this record has value has 0, its not updated in the final sheet.

Lucas Rayala

Hi @VijiNair, that's a good start -- the values don't copy down like an Excel sheet. The "1" in this case is a row reference for the returned Index, which I think you understand. Easiest thing to do to get this to work:

Create a column, NumberedRows, and number the cells sequentially 1,2,3,4, etcs. Add as many values as you think you might need.

In the column you are currently trying to add the formula, update your formula to say:

=IFERROR(INDEX(COLLECT({Data 1 Name}, {Data 1 Range 2}, "YES"), NumberedRows@row),"")

When you have this working, right click the cell and make this a column formula by selecting "Column formula" (you have to be a sheet Admin or Owner to do this, otherwise just copy and paste it).

The "IFERROR" is to keep the formula from returning an error if the you have more numbered rows in "Numbered Rows" than you will be returning from your source sheet.

VijiNair

=INDEX(COLLECT({Data 1 Name}, {Data 1 Range 2}, "YES"), 1)

So this formula is giving me correct results, but I am unable to drag it down to other rows. Whenever is drag the value "1" is still the same and not updating to 1..2..3.. son on. Please help.

Lucas Rayala

Hi @VijiNair, that's a good start -- the values don't copy down like an Excel sheet. The "1" in this case is a row reference for the returned Index, which I think you understand. Easiest thing to do to get this to work:

Create a column, NumberedRows, and number the cells sequentially 1,2,3,4, etcs. Add as many values as you think you might need.

In the column you are currently trying to add the formula, update your formula to say:

=IFERROR(INDEX(COLLECT({Data 1 Name}, {Data 1 Range 2}, "YES"), NumberedRows@row),"")

When you have this working, right click the cell and make this a column formula by selecting "Column formula" (you have to be a sheet Admin or Owner to do this, otherwise just copy and paste it).

The "IFERROR" is to keep the formula from returning an error if the you have more numbered rows in "Numbered Rows" than you will be returning from your source sheet.

VijiNair

Thank you Lucas. That helps!!

Lucas Rayala

No problem @VijiNair happy to help!