INDEX Collect with date range

Options

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

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Options

    @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?

    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.

  • LB432341
    Options

    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
    Options

    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
    Mark.poole ✭✭✭✭✭✭
    Options

    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.

  • LB432341
    Options

    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

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Options

    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.

  • LB432341
    Options

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!