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
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!