Conditional formatting for table header if table range contains non-numeric characters

[ausswe]

Hi, I've been trying to find a way to use conditional formatting for a the header if the column for that table contains non-numeric characters as I come across this happening when receiving exported data.

I have tried using ISTEXT and ISNUMBER together with an IF statement but haven't gotten it to work - and I read that those can't be used in an array so that might be why, so I thought I'd check if anyone has a clever way to do this:

In my table column C should contain numbers only (in C2:Cxxx), I would like the header (C1) to change apparence if a non-numeric value is present in C2:Cxxx. I have tried to use different ranges and even tried using named ranges at this would be more efficient as the data range changes (C1 = DATA[[#Headers];[NUMBER]] and C2:Cxxx = DATA[NUMBER]).

If conditional formatting is not an option - another option would be to have a text of warning to appear in a cell on the cheat (moving the data down one row and add a formula to check in C1 for example).

Any suggestions would be appreciated!

 

[Peter_SSs]

Does this do what you want?

ausswe.xlsm
	A	B	C	D
1	Hdr1	Hdr2	NUMBER	Hdr4
2	data		4
3	data		5
4	data		15
5	data		xx
6	data
7	data		26
8	data
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1	Expression	=COUNT(INDIRECT("DATA[NUMBER]"))<>COUNTA(INDIRECT("DATA[NUMBER]"))	text	NO

 

[kvsrinivasamurthy]

Formula for C2:C15. If range contains text value=TRUE else FALSE.

=ISERROR(SUMPRODUCT(1*((0+$C$2:$C$15)=$C$2:$C$15)))

NOTE: Text formatted numbers are also treated as text by excel.

 

[ausswe]

Thanks Peter - that formula seems to do the trick!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.