Index Collect value based on a date

Options

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!


Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    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

  • Michael Culley
    Michael Culley ✭✭✭✭✭
    edited 12/29/22
    Options

    What if you change the

    [This Sheet Date]@row >= @ cell to

    >=[This Sheet Date]@row

    So basically {DateValueEffective},>=[This Sheet Date]@row

  • Kayla
    Kayla ✭✭✭✭✭
    Options
  • Michael Culley
    Michael Culley ✭✭✭✭✭
    edited 12/29/22
    Options

    Ohhh I see. Just curious, Is this sheet being populated via a form?

  • Michael Culley
    Michael Culley ✭✭✭✭✭
    Options

    I'll bet if you sort your TEST Database Log Changes by Date Descending it would work. If that sheet is being populated by a form you could have new entries be placed at the bottom instead of the top which would put the newer dates at the top.

  • Kayla
    Kayla ✭✭✭✭✭
    Options

    @Michael Culley, it's being populated with a copy row automation, so I cannot control the sort. But sorting doesn't do the trick with this either.

  • Michael Culley
    Michael Culley ✭✭✭✭✭
    Options

    @Paul Newcome Calling in an expert lol

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    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).

  • Kayla
    Kayla ✭✭✭✭✭
    Options

    That worked and makes complete sense, but certainly nothing that would have crossed my mind! THANK YOU.

    I am however not able to get rid of the error showing if the date doesn't match a date on the referenced sheet, even with IFERROR. I've never come across this before... I need the value to default to 1% if there isn't a match, and I planned to use IFERROR, but this formula doesn't like that for some reason. I haven't messed around with it, but if you see anything obvious - please share.



  • Kayla
    Kayla ✭✭✭✭✭
    Options

    I fixed it with this formula:

    =IF(COUNTIFS({DateValueEffective}, @cell <= [This Sheet Date]@row, {Manager}, @cell = [Employee Manager]@row) = 0, 0.01, INDEX(COLLECT({VALUE}, {Manager}, @cell = [Employee Manager]@row), COUNTIFS({DateValueEffective}, @cell <= [This Sheet Date]@row, {Manager}, @cell = [Employee Manager]@row)))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Happy to help. 👍️


    That's an odd error to run across. Usually that means you are pulling in the wrong type for the column type such as pulling a number into a date type column or a date into a text/number column, and the IFERROR should have gotten rid of that. 🤔


    Glad to see you were able to get it sorted though.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!