0

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.

Improve this question

2 Answers 2

Reset to default
0

I think this will work for finding current. I've tested it up to a point, but am guessing that ignoring one characteristic is, in this case, a non-issue.

First, add a column between B and C (Assignee becomes D henceforth). In this column, put in this formula:

=IF(NOW()-A2>0,IF(NOW()-B2<0,"Yes","No"),"No")

That should yield one row with Yes and the rest with No (for current slot).

Now, in your cell for current assignee, you put:

=INDEX($C$2:$D$4, MATCH("Yes", $C$2:$C$4,0), 2)

For previous: =INDEX($C$2:$D$4, MATCH("Yes", $C$2:$C$4,0) - 1, 2)
For next: =INDEX($C$2:$D$4, MATCH("Yes", $C$2:$C$4,0) + 1, 2)

Normally, MATCH wants the search column sorted, but because we should only have one Yes in the column ever, I think (this is the guess part) that we can ignore that restriction.

My test spreadsheet was only 3 rows, so YMMV.

You will need some error checking for previous when current is the first line, etc., and naming your source range is probably a good idea.

You also may want to hide the extra column.

Improve this answer
5
  • I'm not much familiar with INDEX & MATCH, except that they're supposed to be superior in certain ways to VLOOKUP. That said, I think this is how I'd implement your suggestion with VLOOKUP, that also allows for easy coverage of Previous & Next: 1. C2=IF(C1="Current","Next",IF(C3="Current","Previous",IF(AND(NOW()>A2,NOW()<B2),"Current",""))). 2. Copy C2 down to the end. 3. For "Current" we'd do =VLOOKUP(C:D,"Current",2,FALSE). 4. Write similar formulas as in 3, for "Previous" & "Next".
    – Iszi
    Commented May 18, 2015 at 17:57
  • Bah. Looks like my idea might have caused a circular reference glitch.
    – Iszi
    Commented May 18, 2015 at 18:01
  • Nice try and it works for the current. However for previous and next items it works only if the list is sorted, but as stated in the question it's not always the case.
    – Máté Juhász
    Commented May 18, 2015 at 18:13
  • @MátéJuhász True. I'm not sure "Previous" and "Next" can really be figured properly without sorting, unless we have non-variable durations for the windows (we do) and define those in the formula. With sorting though, this will work in the "Helper Column" for all three: =IF(NOW()>=A2,IF(NOW()<=B2,"Current",IF(NOW()<=B3,"Previous","")),IF(AND(NOW()>=A1,NOW()<=B1),"Next",""))
    – Iszi
    Commented May 18, 2015 at 18:18
  • "Assuming that the spreadsheet stays in proper order (ascending by START)..." Hence the assumptions about previous/next.
    – Oort
    Commented May 18, 2015 at 18:37
0

Ordered input:

Current assignee: currRow=match(now(),A:A,1) - as your data is ordered this will find the current row, no need for a helper column. Set the name of the cell counting this to currRow just for easier referencing.
Assignee: =indirect("C"& currRow + x) - x: -1, 0, 1 for previous, current and next rows respectively.

Non-ordered input:

Current row: currRow=match(max(if(A:A>now(),"",A:A)),A:A,0) - this is an array formula, so you need to enter it with CTRL+SHIFT+ENTER.
Current assignee: Same formula as before.
Previous: =indirect("C" & match(max(if(A:A>=indirect("A" & currRow),"",A:A)),A:A,0)) - also an array formula.
Next: =indirect("C" & match(min(if(A:A<=indirect("A" & currRow),"",A:A)),A:A,0)) - and still an array formula.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .