Index Collect value based on a date
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
-
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).
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
What if you change the
[This Sheet Date]@row >= @ cell to
>=[This Sheet Date]@row
So basically {DateValueEffective},>=[This Sheet Date]@row
-
Didn't work. This is what I get..
-
Ohhh I see. Just curious, Is this sheet being populated via a form?
-
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.
-
@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.
-
@Paul Newcome Calling in an expert lol
-
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).
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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.
-
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)))
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!