Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 :( |
#2
|
|||
|
|||
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 :( |
#3
|
|||
|
|||
=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 :( |
#4
|
|||
|
|||
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 :( |
#5
|
|||
|
|||
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 :( |
#6
|
|||
|
|||
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 :( |
#7
|
|||
|
|||
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 :( |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formating & Rand() function | Excel Discussion (Misc queries) | |||
Conditional Formating | Excel Discussion (Misc queries) | |||
Conditional formatting on cells with a VLOOKUP formula in them | Excel Discussion (Misc queries) | |||
vlookup & conditional formatting | Excel Worksheet Functions | |||
more than 3 conditional formating in excel | Excel Discussion (Misc queries) |