Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using lookup functions with conditions and constantly changing dat
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using lookup functions with conditions and constantly changingdat
(-) 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? 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using lookup functions with conditions and constantly changing dat
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using lookup functions with conditions and constantly changing dat
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using lookup functions with conditions and constantly changing
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! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using lookup functions with conditions and constantly changingdat
Excel 2007
Dynamic ranges, slightly more colorful: http://www.mediafire.com/file/xmmjwi...01_10_09a.xlsx |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using lookup functions with conditions and constantly changing
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? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using lookup functions with conditions and constantly changing
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$1 1TODAY())*(Sheet1!$C$3:$C$11-TODAY())0,(Sheet1!$A$3:$A$11=$B$1)*(Sheet1!$C$3:$ C$11TODAY())*(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 --- "(-) Ions" wrote: 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! |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using lookup functions with conditions and constantly changing
Errata, B2's array formula should be:
Put in B2, array-enter, ie press CTRL+SHIFT+ENTER to confirm the formula: =INDEX(Sheet1!$B$3:$B$11,MATCH(MIN(IF((Sheet1!$A$3 :$A$11=$B$1)*(Sheet1!$C$3:$C$11TODAY())*(Sheet1!$ C$3:$C$11-TODAY())0,(Sheet1!$A$3:$A$11=$B$1)*(Sheet1!$C$3:$ C$11TODAY())*(Sheet1!$C$3:$C$11-TODAY()))),(Sheet1!$A$3:$A$11=$B$1)*(Sheet1!$C$3:$ C$11TODAY())*(Sheet1!$C$3:$C$11-TODAY()),0)) -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using lookup functions with conditions and constantly changing
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 todays 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(She et1!$C$3:$C$11),IF((Sheet1!$A$3:$A$11=$B$1)*(Sheet 1!$C$3:$C$11TODAY())*(Sheet1!$C$3:$C$11-TODAY())0,(Sheet1!$A$3:$A$11=$B$1)*(Sheet1!$C$3:$ C$11TODAY())*(Sheet1!$C$3:$C$11-TODAY())))),IF(ISNUMBER(Sheet1!$C$3:$C$11),IF((She et1!$A$3:$A$11=$B$1)*(Sheet1!$C$3:$C$11TODAY())*( Sheet1!$C$3:$C$11-TODAY())0,(Sheet1!$A$3:$A$11=$B$1)*(Sheet1!$C$3:$ C$11TODAY())*(Sheet1!$C$3:$C$11-TODAY()))),0)) -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using lookup functions with conditions and constantly changing
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! |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using lookup functions with conditions and constantly changing
Delighted to hear that.
Thanks for the feedback. -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- "(-) Ions" wrote in message ... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding a minimum value in a cell that is constantly changing. | Excel Worksheet Functions | |||
Linking to Workbooks with Constantly Changing Names | Excel Discussion (Misc queries) | |||
How torecord the highest value in a set of constantly changing num | Excel Worksheet Functions | |||
sorting a constantly changing set of rows in VBA | Excel Discussion (Misc queries) | |||
Nested IF functions and 3 conditions | Excel Worksheet Functions |