INDEX Collect with date range
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.
Answers
-
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?
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
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.
-
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.
-
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)
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
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
-
You have an extra } after Report Date.
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!