I have two "Slack Additions" and "Todays Location".
Slack Additions is populated from a Slack Workflow. It is roster of peoples names, location, location start date and location end date. Names can appear on this sheet multiple times, with different locations for different time periods.
I am trying to write a formula in "Todays Location" where a name only appears once but the location updates dynamically based on a formula that indexes the names from Sheet A and calls the location based on if todays date falls between the location start and end date.
Slack Additions:
Todays Location:
So far, I've tried using Index Collect but it keeps returning an invalid value.
=INDEX(COLLECT({A-Slack Additions Range 4}, {A-Slack Additions Range 2}, TODAY() >= {A-Slack Additions Range 2}, {A-Slack Additions Range 3}, TODAY() <= {A-Slack Additions Range 3}), 1)
What am I doing wrong?