Help on Index(collect) to drag the values down
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.
Best Answer
-
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.
Answers
-
=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.
-
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.
-
Thank you Lucas. That helps!!
-
No problem @VijiNair happy to help!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives