Using lookup functions with conditions and constantly changing dat

I

(-) Ions

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?
 
S

smartin

(-) Ions said:
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?
Click to expand...

Array formulae can take care of this. Array formulae must be committed
with Ctrl+Shift+Enter (not just Enter).

I put your data with headers in A3:Cx, and placed "today's date" value
in C1.

The serial formula for "Frank" is:
=SMALL(IF(($A$4:$A$12="Frank")*($C$4:$C$12>$C$1),$B$4:$B$12),1)

The text-with-due date formula is:
="Due on: " &
TEXT(SMALL(IF(($A$4:$A$20="Frank")*($C$4:$C$20>$C$1),$C$4:$C$20),1),"mm/dd")

You could substitute cell references in place of hard-coded names to
make these more versatile.
 
M

Max

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$11>TODAY()),),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
 
I

(-) Ions

Thank you. Both posts helped a lot and both seem to work perfectly for the
example I posted. Are any of the functions used in those limited to how many
columns you can have and search through? As you may have guessed, the
example I gave is a simplified spreadsheet based on the one I am trying to
improve at work. The actual one has an employee name and serial number
assigned to them in the first two columns and then most of the columns to the
right are many different due dates for different types of things that are due
for that person for the serial number to which they are assigned. So Task A
might have a due date in column C as in the example I gave and say that
column D has a spot for the employees' initials when they finish Task A and
column E is the date they finished Task A. Column F then would be the due
dates for another task we'll call Task B. And columns G and H would be just
like D and E but for Task B. This pattern repeats for a few different types
of tasks that all must be done for a given serial number and the employee
assigned usually does each of these tasks.

With that said, the example second sheet I showed would be just like that
but with more rows, one row for each type of task showing the upcoming serial
and due date but for each task.

Will this be accomplished in the exact same way but with larger column ranges?

In the actual spreadsheet it has about 100 rows and columns A through AM or
so, so there is a lot of data there.

Thanks so much again for the help so far and I think it is almost exactly
what I need. Thanks to both posters too. Both suggestions helped a lot.
 
I

(-) Ions

Nevermind. Disregard the last message (except for the parts where I said
"thanks!"). I took my example spreadsheet and expanded on it and made it
more complicated with more due dates for more tasks and simply replaced the
callouts in the formulas and it worked great. I should be able to implement
this as soon as I go back to work on Monday. Thanks again!
 
I

(-) Ions

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!
 
M

Max

In future please start new threads for new queries. This thread is closed.

The latest scenario you paint is more involved now.
Think you could try this ...

In Sheet2,
With the name in B1, eg: Frank
Put in B2, array-enter, ie press CTRL+SHIFT+ENTER to confirm the formula
=MIN(IF((Sheet1!$A$3:$A$11=$B$1)*(Sheet1!$C$3:$C$11>TODAY())*(Sheet1!$C$3:$C$11-TODAY())>0,(Sheet1!$A$3:$A$11=$B$1)*(Sheet1!$C$3:$C$11>TODAY())*(Sheet1!$C$3:$C$11-TODAY())))
This will extract the serial # corresponding to the earliest due date for
the name in B1 where there could be multiple due dates involved. It will
cover the possibility you mention that the due dates for a particular name
may not appear in chronologic order, eg the earlier due date may appear lower
down in the col.

Then in C2, normal ENTER
=INDEX(Sheet1!C$3:C$11,MATCH(1,INDEX((Sheet1!$A$3:$A$11=$B$1)*(Sheet1!$B$3:$B$11=B2),),0))
Format C2 as date, to extract the due date corresponding to the serial#
extracted in B2 for the name in B1.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
 
M

Max

Errata, B2's array formula should be:
Put in B2, array-enter, ie press CTRL+SHIFT+ENTER to confirm the formula
Click to expand...
=INDEX(Sheet1!$B$3:$B$11,MATCH(MIN(IF((Sheet1!$A$3:$A$11=$B$1)*(Sheet1!$C$3:$C$11>TODAY())*(Sheet1!$C$3:$C$11-TODAY())>0,(Sheet1!$A$3:$A$11=$B$1)*(Sheet1!$C$3:$C$11>TODAY())*(Sheet1!$C$3:$C$11-TODAY()))),(Sheet1!$A$3:$A$11=$B$1)*(Sheet1!$C$3:$C$11>TODAY())*(Sheet1!$C$3:$C$11-TODAY()),0))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
 
M

Max

OP: > .. It works well; the only (and hopefully final) problem I run into is
that the page with all the dates and names is not always populated with data
all the way to the bottom because it is updated over time. The above formula
returns a “#VALUE!†error because it has blank cells in the array subtract
today’s date. I have been trying to use ISERROR, ISBLANK, and ISNUMBER but
having issues with it.


Think ISNUMBER can be added as a front check to exclude null string: "" or
text returns within the dates source range

I got it up working with this revised rendition in B2, array-entered:
=INDEX(Sheet1!$B$3:$B$11,MATCH(MIN(IF(ISNUMBER(Sheet1!$C$3:$C$11),IF((Sheet1!$A$3:$A$11=$B$1)*(Sheet1!$C$3:$C$11>TODAY())*(Sheet1!$C$3:$C$11-TODAY())>0,(Sheet1!$A$3:$A$11=$B$1)*(Sheet1!$C$3:$C$11>TODAY())*(Sheet1!$C$3:$C$11-TODAY())))),IF(ISNUMBER(Sheet1!$C$3:$C$11),IF((Sheet1!$A$3:$A$11=$B$1)*(Sheet1!$C$3:$C$11>TODAY())*(Sheet1!$C$3:$C$11-TODAY())>0,(Sheet1!$A$3:$A$11=$B$1)*(Sheet1!$C$3:$C$11>TODAY())*(Sheet1!$C$3:$C$11-TODAY()))),0))

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---
 
I

(-) Ions

Thank you, Max. This general method worked very well. My actual spreadsheet
is more complicated than the example I originally gave, but I eventually got
it all to work like I wanted using all the suggestions you gave.

Thanks again!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How do I count the number of blank cells 0
Sorting data in new sheet. 3
IF ARRAY with multiple conditions. 1
help with Sum in between dates 12
count not-overlapping days in a "Table" with Beginning and EndingsDates Specified 0
Calculating overdue date fields 3
Copy to another worksheet IF 3
Excel date intervals look up 1

Top