Thanks again for this. I ended up using this (modified to fit my needs)
and has worked wonderfully for the last few weeks. One problem I have just
run into needs a little work. What if the dates wouldn't always necessarily
be in chronological order? For example, if Frank's assigned serial of 14 was
actually due after 17, how would you get these functions to pull that date
instead of 14's date? With the index and match functions, it is looking for
a date that is greater than or equal to today's date, so if both the next two
serials assigned to Frank have due dates greater than or equal to today's
date, it will find the first one, even if that one is actually due after the
following one. My concern here is that something would get missed because my
function is showing a date further off for something that is due, but a
person may actually have a different one due sooner. Hopefully that makes
sense.

Thanks!




"Max" wrote:

Lightly tested ok, here's one play to try over there

Source data assumed in A3:C11 in Sheet1
In Sheet2,
With the name in B1, eg: Frank
Put in B2, normal ENTER:
=INDEX(Sheet1!B$3:B$11,MATCH(1,INDEX((Sheet1!$A$3: $A$11=$B$1)*(Sheet1!$C$3:$C$11TODAY()),),0))
Copy B2 to C2. Format C2 as date. Frame it up likewise for the other names
(just amend the $B$1 reference). Adapt the ranges to suit your actuals.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---
"(-) Ions" wrote:
I want to have a sheet in my workbook that looks through another sheet for a
person's name (there will be many instances of each person's name) and checks
a due date assigned to that person (compares it to the current date) and
returns a serial number assigned to that person (which will be a different
serial number for different instances of the same name. This of course
should pull one serial number for one employee if the current day is a
certain number of days away from the due date (for example,7 days). That
being said, when that item is finished and the workbook is opened 8 or 9 days
later, it should pull yet a different serial number that is now the next or
"upcoming" serial based on its due date. See below for an example:

Today 01/09/09

Employee Serial # Due Date
Frank 11 01/04/09
Sam 12 01/08/09
Bill 13 01/09/09
Frank 14 01/11/09
Sam 15 01/12/09
Bill 16 01/15/09
Frank 17 01/18/09
Sam 18 01/20/09
Bill 19 01/23/09


Given that, I want another sheet that looks something like what I've pasted
below:

Frank Sam Bill
Next Serials 14 Due on:01/11 15 Due on:01/12 13 Due on:01/09

Notice that I want it to pull the serial and due date for that serial that
is the next one after the current date. Any that are already past their due
date (assumed to be finished) and any beyond just the next due date should
not show.

I have tried using a combo of "if" functions and "vlookup" etc. Vlookup
wasn't working well because it would search for a name and when it found it,
only the first corresponding entry was returned.

Any ideas on how to accomplish this or will it take macros or something?