Index Collect value based on a date

Options
Kayla
Kayla ✭✭✭✭✭

I want to index a value if the "This Sheet Date" on my final result sheet is greater than "Date Value Effective" on my database sheet.

Screenshots should help. I have a column showing the value that I want it to return. It's showing me the 2% because technically all of the "This Sheet Dates" are greater than the "Date Value Effective", but I want it to update if there is a more recent date entry.

It's right under my nose and I can't see it!

Database Log Changes.png Final Result.png


Best Answer

  • Paul Newcome
    Paul Newcome Community Champion
    Answer βœ“

    Give something like this a try...

    =IFERROR(INDEX(COLLECT({Value}, {Manager}, @cell = [Employee Manager]@row), COUNTIFS({Date}, @cell<= [This Sheet Date]@row, {Manager}, @cell = [Employee Manager]@row)), "")


    Since the reference sheet is being fed via copy row automation, we want to assume that all entries will be in chronological order from top to bottom with new entries going at the bottom of the sheet (so we don't have to actually interact with the reference sheet).


    So what we do is COLLECT all of the {Value} where the {Manager} is what we are looking for. We then INDEX this list of values, and counting how many dates (for that manager) are less than or equal to the sheet date should give us the entry number from the COLLECT function to pull.


    Example:

    We COLLECT all of the values where the manager is John Smith. This gives us a list with 3 entries on it.

    The date on the formula sheet is 06 December 2022. This means our COUNTIFS is going to output the number 2 which tells the INDEX function to pull the second entry from our list generated by the COLLECT function.


    I haven't had any coffee yet today. The explanation may not make much sense, but the formula should work for you. If you need clarification on anything, please don't hesitate, and I will try to explain a different way (after I have had some coffee).

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!