Pulling a Value based on Beginning and End Date.

NCH

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.

sharkasits

@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()))

sharkasits

@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()))

NCH

Thank you so much. That worked great.