VLOOKUP & Conditional Formating Help.
 

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 :(

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)


"Excel'ed Failures" wrote in
message ...
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 :(

=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" wrote:

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)


"Excel'ed Failures" wrote in
message ...
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 :(

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)


"Excel'ed Failures" wrote in
message ...
=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" wrote:

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)


"Excel'ed Failures" wrote in
message ...
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 :(

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

"Bob Phillips" wrote:

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)


"Excel'ed Failures" wrote in
message ...
=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" wrote:

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)


"Excel'ed Failures" wrote in
message ...
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 :(

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)


"Excel'ed Failures" wrote in
message ...
Still no luck, when I manualy type in the date the CF works like a charm.

"Bob Phillips" wrote:

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)


"Excel'ed Failures" wrote in
message ...
=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" wrote:

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)


"Excel'ed Failures"
wrote in
message ...
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 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.

"Bob Phillips" wrote:

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)


"Excel'ed Failures" wrote in
message ...
Still no luck, when I manualy type in the date the CF works like a charm.

"Bob Phillips" wrote:

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)


"Excel'ed Failures" wrote in
message ...
=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" wrote:

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)


"Excel'ed Failures"
wrote in
message ...
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 :(