VLOOKUP & Conditional Formating Help.

[Guest]

Hello Ladies and Gents.

I am having a problem with VLOOKUP and Conditional Formatting. What I am
trying to do is apply conditional formatting to the result of a formula. I
have a list of serial numbers in column A. In Column B I have a VLOOKUP
formula that takes the serial number from column A and compares it to a list
of serial numbers and dates imported from access in column C and D, taking
the date from column D. The result of this formula is a date that
corresponds to the same serial number found in columns A and C. I applied a
conditional format to this date and it doesn't recognize that it is a date at
all. When I type in the value the conditional format works correctly. I
tried to change the format to no avail. Does anyone have any insight to my
problem? Thanks in Advance!

PS I dont want to type in over 500 dates just to get the conditional
formatting to work

 

[Bob Phillips]

I just knocked up a test and it worked fine.

What does the VLOOKUP formula look like, and the CF formula.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

=VLOOKUP(A12,L$7:M$186,2,FALSE)

A12 "Serial Number I am wanting to look up"
L7 to M186 "The range of cells I am looking for matches"
2 "Column to search"
False "Exact Match only"

5A12001 (A12) is found in access imported data (L7:M186) in the second
column and returns the data 8/08/2005

I am using the CF "If cell value is less than H4 (Value is 8/9/2005)" Turn
the text to red. 8/08/2005 is less than 8/09/2005 and should be red.

It works when I type in the value just doesnt work when I try to get it
using the formula. Here is another thing thats strange. When I try to
change the date format of the access imported data (Located in L7 to M186) it
wont change. I tried various date formats and it stays on MM/DD/YYYY
regardless on what I changed it to. You have never let me down Mr. Phillips
thanks in advance!

This is the Formula. I am using

 

[Bob Phillips]

It is probably because the dates are text.

I am not sure where the dates are imported into, but assuming it is column
B, select those cells and us a CF formula of =--B1<$H$4

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Still no luck, when I manualy type in the date the CF works like a charm.

 

[Bob Phillips]

That convinces me it is text. Can you post a workbook somewhere? take a look
at http://www.savefile.com/

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Bob I got it to work. I was copy and pasting the Access data into Excel. It
was reading it as text and the format could not be changed. I used the
export feature to put the Access data into an Excel spreadsheet after that it
was in the right date format. The conditional formating worked fine and I
saved the day. I just hope that they dont dump the gatorade on me like the
comercials.