INDEX Collect with date range

LB432341

Hi

I'm trying to create a historical view of our project RAG Status. We have our status reports submitted into a log which I'll need to reference but also add some dates (such as >11/06/24, < 13/06/24) to the criteria in order to reference the correct & relevant reports.

I've not done this before so I would appreciate any advice.

Mark.poole

@LB432341

All of the criteria would be in the Collect function.

=Index(Collect({Project Status reporting - Current Status Range 3},{Project Status reporting - Current Status Range 3}, >Date(2024,06,11),{Project Status reporting - Current Status Range 3},< Date(2024,06,13)),1)

with out more information I am not able to do more then that. Basically your trying to pull a date from {Project Status reporting - Current Status Range 3} that falls between 11/06/24 and 13/06/24. are there any other criteria?

LB432341

Hi Mark thanks for the reply, sorry I am trying to pull through the Symbols based on the project name & report date being between the specified date. I've added a screenshot below. So basically I want it to Search Sheet for the value in (collumn7@row and then find the report within the specified date and collect the symbol from 'Overall progress'

Hope this makes sense. I'm fairly certain Index Collect is the correct formula however I'm open to suggestions. I can't use a Vlookup as it needs to search up and down the source sheet.

LB432341

Hi Mark thanks for the reply, sorry I am trying to pull through the Symbols based on the project name & report date being between the specified date. I've added a screenshot below. So basically I want it to Search Sheet for the value in (collumn7@row and then find the report within the specified date and collect the symbol from 'Overall progress'

Hope this makes sense. I'm fairly certain Index Collect is the correct formula however I'm open to suggestions. I can't use a Vlookup as it needs to search up and down the source sheet.

Mark.poole

Alright so what your wanting is a few things.

Ensure you have some data on the sheet your pulling to to base your index off of.

=Index(Collect({Overall Progress},{Test Name},[Column 7]@row,{Report Date}, >11/06/24,{Report Date},< 13/06/24),1)

what this does is returns the first item that matches the criteria. IF you have the dates your wanting your information to be greater then and less then on the summary sheet then you can target those instead of actual dates.

=Index(Collect({Overall Progress},{Test Name},[Column 7]@row,{Report Date}, >[Date1]@row,{Report Date},< [Date2]@row),1)

LB432341

https://community.smartsheet.com/discussion/comment/440243#Comment_440243

Hi Again Mark,

Thank you. it seems to be completing the formula now I have updated the references instead of your terms however I'm not getting invalid value. Starting to question if it is user error. Can you identify any mistakes in the below? Do i need to reference the entire sheet or the individual columns??

Thank you

LB432341

https://community.smartsheet.com/discussion/comment/440452#Comment_440452

Mark.poole

You have an extra } after Report Date.

LB432341

Hi Mark, managed to get it to work however went on today and the formula had cleared. 😓 Now I tried again and it won't work again. If you could advise that would be great. I've added screenshots of the ranges too

Range 2 progress collumn

Range 1 Project name collumn

Range 3 Date collumn

This is returning INVALID OPERATION

Much appreciated