ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Help needed: Conditional Formatting for 3 columns, any two of whichmust have data (https://www.excelbanter.com/excel-programming/451254-help-needed-conditional-formatting-3-columns-any-two-whichmust-have-data.html)

pete

Help needed: Conditional Formatting for 3 columns, any two of whichmust have data
 
I have 3 columns, A, B, and C. Each will accept input of exactly 6 alphanumeric characters. Two of the three must be populated.

The Conditional Formatting is used to highlight columns which are not compliant.

I've got one for the 6 characters required (spaces before and after the strings are ignored):

=OR(LEN(TRIM(A1))6,AND(LEN(TRIM(B1))0,LEN(TRIM(C 1))<6))

And I've got formulas for error states when none or all of the columns are populated:

=AND(LEN(TRIM(A1))=0,LEN(TRIM(B1))=0,LEN(TRIM(C1)) =0)

=AND(LEN(TRIM(A1))0,LEN(TRIM(B1))0,LEN(TRIM(C1)) 0)

But my formula for ensuring that 2 of the columns are populated gets fooled by spaces in the columns, which should be ignored:

=COUNTBLANK(A1:C1)<2

My only alternative that i can think of is to do a fairly complicated AND OR formula with all of the acceptable combinations of the 3 columns.

I'm guessing there's a much more elegant, simple option?

Claus Busch

Help needed: Conditional Formatting for 3 columns, any two of which must have data
 
Hi Pete,

Am Thu, 7 Jan 2016 21:53:00 -0800 (PST) schrieb pete:

I have 3 columns, A, B, and C. Each will accept input of exactly 6 alphanumeric characters. Two of the three must be populated.

The Conditional Formatting is used to highlight columns which are not compliant.


select A1:C to the end
Conditional Formatting:

=SUM(LEN(TRIM($A1:$C1)))<12


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Claus Busch

Help needed: Conditional Formatting for 3 columns, any two of which must have data
 
hi Pete,

Am Thu, 7 Jan 2016 21:53:00 -0800 (PST) schrieb pete:

I have 3 columns, A, B, and C. Each will accept input of exactly 6 alphanumeric characters. Two of the three must be populated.


you also can use the Data Validation to check the text length in the
columns
DV = Custom: =LEN(TRIM(A1))=6

Then you can select A1:C to the end and use
=COUNTA($A1:$C1)<3
into CF


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

pete

Help needed: Conditional Formatting for 3 columns, any two ofwhich must have data
 
On Friday, January 8, 2016 at 1:02:31 AM UTC-8, Claus Busch wrote:
Hi Pete,

Am Thu, 7 Jan 2016 21:53:00 -0800 (PST) schrieb pete:

I have 3 columns, A, B, and C. Each will accept input of exactly 6 alphanumeric characters. Two of the three must be populated.

The Conditional Formatting is used to highlight columns which are not compliant.


select A1:C to the end
Conditional Formatting:

=SUM(LEN(TRIM($A1:$C1)))<12


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional


Thanks Claus! I wasn't able to get "SUM" working here, but used "SUMPRODUCT" instead, which seems to do the trick :)

thanks again!


All times are GMT +1. The time now is 08:24 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com