Pulling a Value based on Beginning and End Date.

I am having trouble figuring this one out. I have seen all the posts about using index and collect but have not been able to get any examples I have seen to work. I want the CSR row from one sheet to fill into a specific cell in another schedule based on the date. So, for instance, this week, the name "Nina would appear in the filter this Monday through Wednesday but revert back to Sharica for all Thursdays and Fridays.
This is the Floater Schedule that the value would come from:
And the value would appear in the cell with the word Floater in it on Schedule 2_13_2023.
Any help would be greatly appreciated, as I do not understand the examples I have seen.
Best Answer
-
@NCH Try this formula. I think this is what you're trying to do.
=if(COUNT(COLLECT(CSR:CSR, Beginning:Beginning, <= TODAY(), Ending:Ending, >=TODAY()))>0,INDEX(COLLECT(CSR:CSR, Beginning:Beginning, <= TODAY(), Ending:Ending, >=TODAY()),1),COLLECT(<<column with Sharice>>:<<column with Sharice>>, Thursday:Thursday, <= TODAY(), Friday:Friday, >=TODAY()))
Answers
-
@NCH Try this formula. I think this is what you're trying to do.
=if(COUNT(COLLECT(CSR:CSR, Beginning:Beginning, <= TODAY(), Ending:Ending, >=TODAY()))>0,INDEX(COLLECT(CSR:CSR, Beginning:Beginning, <= TODAY(), Ending:Ending, >=TODAY()),1),COLLECT(<<column with Sharice>>:<<column with Sharice>>, Thursday:Thursday, <= TODAY(), Friday:Friday, >=TODAY()))
-
Thank you so much. That worked great.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.5K Get Help
- 433 Global Discussions
- 152 Industry Talk
- 494 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 506 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 307 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!