[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.

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]

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]

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]

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!