Conditional Format?
Excel2003 ...
Col C ... contains <=5 char text starting with 0 to 9 Col D ... contains <=8 char text starting with 0 to 9 Sometimes ... the next to last char in Col D is a "P" or a "T" ... whenever this occurs the last Char of Col D must match 1st char in Col C ... I would like to conditional format where this does not happen. ie: Col C = 3052 ... Col D = 123456P3 ... = valid value (3) ... no format Col C = 3052 ... Col D = 123456T3 ... = valid value (3) ... no format Col C = 4052 ... Col D = 123456T1 ... = invalid value (4 vs 1) ... format Col C = 4052 ... Col D = 123456P3 ... = invalid value (4 vs 3) ... format Col C = 4052 ... Col D = 123456P4 ... = valid value (4) ... no format Col C = 3317 ... Col D = 3317-123 .... = (non issue ... no "P" or "T") Thanks ... Kha |
Conditional Format?
Use a formula of
=AND(OR(MID(D1,LEN(D1)-1,1)="P",MID(D1,LEN(D1)-1,1)="T"),RIGHT(D1,1)<LEFT(C1)) in the CF -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ken" wrote in message ... Excel2003 ... Col C ... contains <=5 char text starting with 0 to 9 Col D ... contains <=8 char text starting with 0 to 9 Sometimes ... the next to last char in Col D is a "P" or a "T" ... whenever this occurs the last Char of Col D must match 1st char in Col C ... I would like to conditional format where this does not happen. ie: Col C = 3052 ... Col D = 123456P3 ... = valid value (3) ... no format Col C = 3052 ... Col D = 123456T3 ... = valid value (3) ... no format Col C = 4052 ... Col D = 123456T1 ... = invalid value (4 vs 1) ... format Col C = 4052 ... Col D = 123456P3 ... = invalid value (4 vs 3) ... format Col C = 4052 ... Col D = 123456P4 ... = valid value (4) ... no format Col C = 3317 ... Col D = 3317-123 .... = (non issue ... no "P" or "T") Thanks ... Kha |
Conditional Format?
Awesome ... works great ... Thanks ... Kha
"Bob Phillips" wrote: Use a formula of =AND(OR(MID(D1,LEN(D1)-1,1)="P",MID(D1,LEN(D1)-1,1)="T"),RIGHT(D1,1)<LEFT(C1)) in the CF -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ken" wrote in message ... Excel2003 ... Col C ... contains <=5 char text starting with 0 to 9 Col D ... contains <=8 char text starting with 0 to 9 Sometimes ... the next to last char in Col D is a "P" or a "T" ... whenever this occurs the last Char of Col D must match 1st char in Col C ... I would like to conditional format where this does not happen. ie: Col C = 3052 ... Col D = 123456P3 ... = valid value (3) ... no format Col C = 3052 ... Col D = 123456T3 ... = valid value (3) ... no format Col C = 4052 ... Col D = 123456T1 ... = invalid value (4 vs 1) ... format Col C = 4052 ... Col D = 123456P3 ... = invalid value (4 vs 3) ... format Col C = 4052 ... Col D = 123456P4 ... = valid value (4) ... no format Col C = 3317 ... Col D = 3317-123 .... = (non issue ... no "P" or "T") Thanks ... Kha |
All times are GMT +1. The time now is 04:10 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com