Our group has an Excel sheet that's used to track rotating responsibilities. A simple example would be laid out like this:
- Column A: START has dates defining the start of the time period represented by each line item.
- Column B: END has dates defining the end of the time period represented by each line item.
- Column C: ASSIGNEE has strings representing the person assigned to the given responsibility during the time period defined by START and END on the same line.
The list itself is usually in ascending order by START date. However, it is possible that the sheet may end up re-arranged.
There should not be any overlap between time periods defined in the list.
What I want to add is an informational section with three values (in separate cells):
- PREVIOUS would be the ASSIGNEE corresponding to the time period immediately before the current one.
- CURRENT would be the ASSIGNEE currently responsible.
- NEXT would be the ASSIGNEE corresponding to the time period after the one we're currently in.
Assuming that the spreadsheet stays in proper order (ascending by START), returning PREVIOUS and NEXT should be easy after the formula for CURRENT is figured out. But I'm not even sure exactly where to start for that either.