VLOOKUP with a date, from A1 = Now()

[Dale G]

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]

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]

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]

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 here:
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.)