Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Between dates table lookup.
Hello,
Ok I have the following table: In column A there are a list of dates - the start date of the week. In columns B there is another list of dates - the end date of the week. In column C there is a code. For example: A B C 06/12/04 12/12/04 1234 13/12/04 19/12/04 5678 2012/04 26/12/04 9101 and so on. I would either like to create a forumula for a cell, or some VB code that I could attach to a command button that would get the current date, then check to see if it falls on or between the dates in column A & B, then return the correpsonding code from C. e.g. If today's date is 14/12/04, the formula or code would look in column A & B, find that it falls beween the dates 13/12/04 & 19/12/04, then return the code "5678." I'm not sure how to go about this... any help appreciated! thanks Michael |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Between dates table lookup.
Hi
formula solution: =VLOOKUP(TODAY(),A1:C100,3,TRUE) Note: you have to sort column A scending -- Regards Frank Kabel Frankfurt, Germany schrieb im Newsbeitrag ... Hello, Ok I have the following table: In column A there are a list of dates - the start date of the week. In columns B there is another list of dates - the end date of the week. In column C there is a code. For example: A B C 06/12/04 12/12/04 1234 13/12/04 19/12/04 5678 2012/04 26/12/04 9101 and so on. I would either like to create a forumula for a cell, or some VB code that I could attach to a command button that would get the current date, then check to see if it falls on or between the dates in column A & B, then return the correpsonding code from C. e.g. If today's date is 14/12/04, the formula or code would look in column A & B, find that it falls beween the dates 13/12/04 & 19/12/04, then return the code "5678." I'm not sure how to go about this... any help appreciated! thanks Michael |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Between dates table lookup.
formula version
=SUMPRODUCT(((A1:A10<=TODAY())*(B1:B10=TODAY())=1 )*(C1:C10)) -- HTH RP (remove nothere from the email address if mailing direct) wrote in message ... Hello, Ok I have the following table: In column A there are a list of dates - the start date of the week. In columns B there is another list of dates - the end date of the week. In column C there is a code. For example: A B C 06/12/04 12/12/04 1234 13/12/04 19/12/04 5678 2012/04 26/12/04 9101 and so on. I would either like to create a forumula for a cell, or some VB code that I could attach to a command button that would get the current date, then check to see if it falls on or between the dates in column A & B, then return the correpsonding code from C. e.g. If today's date is 14/12/04, the formula or code would look in column A & B, find that it falls beween the dates 13/12/04 & 19/12/04, then return the code "5678." I'm not sure how to go about this... any help appreciated! thanks Michael |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Between dates table lookup.
Here is a code example if u choose to attach to a button. the answer will
show in a message unless u modify the code to place it in a cell. Sub Macro1() Dim now As Date Dim msg As TextEffectFormat Range("a1").Select now = Range("e1").Value Do Until ActiveCell = "" If ActiveCell <= now And ActiveCell.Offset(0, 1) = now Then ans = ActiveCell.Offset(0, 2).Value MsgBox (ans) Exit Do Else ActiveCell.Offset(1, 0).Activate End If Loop End Sub HTH Devin " wrote: Hello, Ok I have the following table: In column A there are a list of dates - the start date of the week. In columns B there is another list of dates - the end date of the week. In column C there is a code. For example: A B C 06/12/04 12/12/04 1234 13/12/04 19/12/04 5678 2012/04 26/12/04 9101 and so on. I would either like to create a forumula for a cell, or some VB code that I could attach to a command button that would get the current date, then check to see if it falls on or between the dates in column A & B, then return the correpsonding code from C. e.g. If today's date is 14/12/04, the formula or code would look in column A & B, find that it falls beween the dates 13/12/04 & 19/12/04, then return the code "5678." I'm not sure how to go about this... any help appreciated! thanks Michael |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Between dates table lookup.
I missread part of the question - here is the code correction
Sub Macro1() Dim now As Date Range("a1").Select now = Date Do Until ActiveCell = "" If ActiveCell <= now And ActiveCell.Offset(0, 1) = now Then ans = ActiveCell.Offset(0, 2).Value MsgBox (ans) Exit Do Else ActiveCell.Offset(1, 0).Activate End If Loop End Sub " wrote: Hello, Ok I have the following table: In column A there are a list of dates - the start date of the week. In columns B there is another list of dates - the end date of the week. In column C there is a code. For example: A B C 06/12/04 12/12/04 1234 13/12/04 19/12/04 5678 2012/04 26/12/04 9101 and so on. I would either like to create a forumula for a cell, or some VB code that I could attach to a command button that would get the current date, then check to see if it falls on or between the dates in column A & B, then return the correpsonding code from C. e.g. If today's date is 14/12/04, the formula or code would look in column A & B, find that it falls beween the dates 13/12/04 & 19/12/04, then return the code "5678." I'm not sure how to go about this... any help appreciated! thanks Michael |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Between dates table lookup.
Thanks guys for your help. All solutions worked, but in the end I
settled for using this formula =IF(OR(TODAY()<MIN(A:A),TODAY()MAX(B:B)),"Date out of range",VLOOKUP(TODAY(),A:C,3,TRUE)) regards Michael On Sat, 11 Dec 2004 08:35:02 -0800, DMoney wrote: I missread part of the question - here is the code correction Sub Macro1() Dim now As Date Range("a1").Select now = Date Do Until ActiveCell = "" If ActiveCell <= now And ActiveCell.Offset(0, 1) = now Then ans = ActiveCell.Offset(0, 2).Value MsgBox (ans) Exit Do Else ActiveCell.Offset(1, 0).Activate End If Loop End Sub " wrote: Hello, Ok I have the following table: In column A there are a list of dates - the start date of the week. In columns B there is another list of dates - the end date of the week. In column C there is a code. For example: A B C 06/12/04 12/12/04 1234 13/12/04 19/12/04 5678 2012/04 26/12/04 9101 and so on. I would either like to create a forumula for a cell, or some VB code that I could attach to a command button that would get the current date, then check to see if it falls on or between the dates in column A & B, then return the correpsonding code from C. e.g. If today's date is 14/12/04, the formula or code would look in column A & B, find that it falls beween the dates 13/12/04 & 19/12/04, then return the code "5678." I'm not sure how to go about this... any help appreciated! thanks Michael |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
using dates for lookup | Excel Worksheet Functions | |||
Lookup between two dates | Excel Worksheet Functions | |||
Lookup between dates | Excel Discussion (Misc queries) | |||
Lookup dates, fiscal period table | Excel Worksheet Functions | |||
Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) |