VLOOKUP only works when =Today() is used in formula, no other dates

[Shawn Morneau]

Hello, and thank you for reading my post.

I have a table of percentages that correspond to each week of the
previous year, beginning 1/1/2006. The lookup value I'm generating is
in the number format YYYYWW.

I want the percentage corresponding to the current week to
automatically load on another sheet when the workbook is opened. To
achieve this I set the E5 formula to '=Today()'. Below today's date
is a 'date override' cell (E7) where the user can enter a different
date to return it's percentage. I use the weeknum function to return
and display the week (E7,G7). I create the lookup value (I5) with the
following function.

=VALUE(IF(E7="",(YEAR(E5)-1)*100+G5,(YEAR(E7)-1)*100+G7))

I then use the following VLOOKUP to return the percentage based on the
above formula's result.

=VLOOKUP(I5,Table!C,2,FALSE)

So long as E5 is not modified, and no override date is entered, the
VLOOKUP functions properly. If I change anything, however, the
VLOOKUP returns #N/A.

- If I add days to E5, '=Today() + 40' the lookup value displays
correctly '200625', but the VLOOKUP returns #N/A. Deleting the '+ 40'
corrects the error.

- Entering an override date also displays the correct lookup value in
I5, but VLOOKUP returns #N/A. Deleting the override date corrects the
error.


I'm having a hard time understanding how, even though the lookup value
is displaying properly, any formula result other than =Today()
corrupts the lookup.

Any insight would be greatly appreciated.

Regards,
Shawn

 

[NoodNutt]

Newbie to Excel 2007, but I use AccessDB.

most date functions that I know are:

Excel = Now()
Access = Date()


HTH