Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP with a date, from A1 = Now()
Is it possible to set a VLOOKUP to retrieve values using a Date, then setting
the date cell to =NOW(), then retain those values after the date. For example, I have a workbook that is used to keep track of arrivel times at a set location. Every sheet in the wookbook is the same location, one sheet for each day of the month. Column B has the vehicle numbers use on a specific day. Each day the drivers use a different vehicle. The vehicles are entered in a feeder sheet each day in a different workbook. My idea is to have column B of the location workbook retrieve the vehicle numbers on the date the location sheet is being used. So far this is all I have in column B. =IF($A$1=DATE(2009,12,1),VLOOKUP(A3,Feeder!$F$2:$G $121,2,0),"") is it possible to set A1 to NOW() and still retrieve the vehicle numbers? Any help is appreciated. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP with a date, from A1 = Now()
First, =now() includes the date and time, so:
=if(a1=date(...), ... Will only match if you're lucky enough to calculate at Midnight of that date. You could use: =today() in a1 or modify the formula to ignore the time: =if(int(a1)=date(...), ... But the real problem is that the formula will not maintain the results if you leave it a formula. Even when calculation is set to manual, you'll find that that cell will recalc sometime when you didn't want it to. I think the best bet would be to convert the results of the formula to a value (edit|copy, edit|paste special|values). Dale G wrote: Is it possible to set a VLOOKUP to retrieve values using a Date, then setting the date cell to =NOW(), then retain those values after the date. For example, I have a workbook that is used to keep track of arrivel times at a set location. Every sheet in the wookbook is the same location, one sheet for each day of the month. Column B has the vehicle numbers use on a specific day. Each day the drivers use a different vehicle. The vehicles are entered in a feeder sheet each day in a different workbook. My idea is to have column B of the location workbook retrieve the vehicle numbers on the date the location sheet is being used. So far this is all I have in column B. =IF($A$1=DATE(2009,12,1),VLOOKUP(A3,Feeder!$F$2:$G $121,2,0),"") is it possible to set A1 to NOW() and still retrieve the vehicle numbers? Any help is appreciated. -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP with a date, from A1 = Now()
Thanks Dave,
I would need to copy, then paste special, Values. Then just use the standard VLOOKUP. = VLOOKUP(A3,Feeder!$F$2:$G$121,2,0) Would VBA be able to save the formula as a Value? "Dave Peterson" wrote: First, =now() includes the date and time, so: =if(a1=date(...), ... Will only match if you're lucky enough to calculate at Midnight of that date. You could use: =today() in a1 or modify the formula to ignore the time: =if(int(a1)=date(...), ... But the real problem is that the formula will not maintain the results if you leave it a formula. Even when calculation is set to manual, you'll find that that cell will recalc sometime when you didn't want it to. I think the best bet would be to convert the results of the formula to a value (edit|copy, edit|paste special|values). Dale G wrote: Is it possible to set a VLOOKUP to retrieve values using a Date, then setting the date cell to =NOW(), then retain those values after the date. For example, I have a workbook that is used to keep track of arrivel times at a set location. Every sheet in the wookbook is the same location, one sheet for each day of the month. Column B has the vehicle numbers use on a specific day. Each day the drivers use a different vehicle. The vehicles are entered in a feeder sheet each day in a different workbook. My idea is to have column B of the location workbook retrieve the vehicle numbers on the date the location sheet is being used. So far this is all I have in column B. =IF($A$1=DATE(2009,12,1),VLOOKUP(A3,Feeder!$F$2:$G $121,2,0),"") is it possible to set A1 to NOW() and still retrieve the vehicle numbers? Any help is appreciated. -- Dave Peterson . |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP with a date, from A1 = Now()
Sure.
If the range of cells always had the same address, you could just record a macro when you do it once. Then you could modify the macro so the guts would look like: with activesheet.Range("F8:I16") .Copy .PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks:=False, _ Transpose:=False end with If the range varied each day, you could make sure you selected the range first and then ran a macro that only worked on that selection. (Make sure it's a single contiguous range, too). with Selection .Copy .PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks:=False, _ Transpose:=False end with If you're new to macros: Debra Dalgleish has some notes how to implement macros he http://www.contextures.com/xlvba01.html David McRitchie has an intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm (General, Regular and Standard modules all describe the same thing.) Dale G wrote: Thanks Dave, I would need to copy, then paste special, Values. Then just use the standard VLOOKUP. = VLOOKUP(A3,Feeder!$F$2:$G$121,2,0) Would VBA be able to save the formula as a Value? "Dave Peterson" wrote: First, =now() includes the date and time, so: =if(a1=date(...), ... Will only match if you're lucky enough to calculate at Midnight of that date. You could use: =today() in a1 or modify the formula to ignore the time: =if(int(a1)=date(...), ... But the real problem is that the formula will not maintain the results if you leave it a formula. Even when calculation is set to manual, you'll find that that cell will recalc sometime when you didn't want it to. I think the best bet would be to convert the results of the formula to a value (edit|copy, edit|paste special|values). Dale G wrote: Is it possible to set a VLOOKUP to retrieve values using a Date, then setting the date cell to =NOW(), then retain those values after the date. For example, I have a workbook that is used to keep track of arrivel times at a set location. Every sheet in the wookbook is the same location, one sheet for each day of the month. Column B has the vehicle numbers use on a specific day. Each day the drivers use a different vehicle. The vehicles are entered in a feeder sheet each day in a different workbook. My idea is to have column B of the location workbook retrieve the vehicle numbers on the date the location sheet is being used. So far this is all I have in column B. =IF($A$1=DATE(2009,12,1),VLOOKUP(A3,Feeder!$F$2:$G $121,2,0),"") is it possible to set A1 to NOW() and still retrieve the vehicle numbers? Any help is appreciated. -- Dave Peterson . -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup by date | Excel Worksheet Functions | |||
vlookup using date | Excel Discussion (Misc queries) | |||
VLOOKUP using date problem | Excel Worksheet Functions | |||
Vlookup and Date | Excel Worksheet Functions | |||
Date VLookup | Excel Worksheet Functions |