Conditional formatting using Search function

[WendyHubard]

Hello Guru's
I'm trying to us conditional formatting on a table based on values enter in cell C1 and using the search function. It sort of works, but if C1 is empty/blank, the format is applied to all cells. Any Clues. Have had to use screen shots due to work security, but happy to upload a modified spreadsheet. Have tried with and without the first Formula. Any help greatly appreciated.

CF:

IF C! is BLANK

 

[Peter_SSs]

The end users don't search by the binder or index numbers.

So what actual columns did you mean by ..

end user could type a name, order name, purchaser etc.

.. given that none of those red values exactly appear as headings in your data and "etc" is an unknown quantity?
In any case, doesn't the option I gave without the need for helper columns do the job?

If you did want to keep using the helper columns you could simplify all formulas by ..

• Only concatenate the relevant columns in col K (I have used cols C, D & E below but there may be more depending on the answer to my first question above).
• Simpler formula in col L - errors do not matter for CF, they are treated as FALSE values (column could be hidden)
• Simpler formula in the Conditional formatting (no need for =TRUE)

25 03 22.xlsm
	A	B	C	D	E						K	L
1		Search	Buyer 74
2
3	Binder Name	Index Number	Purchzspr	Order Number	Unique						ID
4	2020	57	Buyer 90	OrdNum 28456	Unique 926796						Buyer 90OrdNum 28456Unique 926796	#VALUE!
5	2023	2	Buyer 48	OrdNum 77550	Unique 451807						Buyer 48OrdNum 77550Unique 451807	#VALUE!
6	2019	13	Buyer 21	OrdNum 60514	Unique 291593						Buyer 21OrdNum 60514Unique 291593	#VALUE!
7	2020	9	Buyer 41	OrdNum 35296	Unique 839225						Buyer 41OrdNum 35296Unique 839225	#VALUE!
8	2020	7	Buyer 74	OrdNum 42023	Unique 136741						Buyer 74OrdNum 42023Unique 136741	1
9	2025	26	Buyer 77	OrdNum 51646	Unique 321216						Buyer 77OrdNum 51646Unique 321216	#VALUE!
10	2019	12	Buyer 83	OrdNum 31622	Unique 524089						Buyer 83OrdNum 31622Unique 524089	#VALUE!
11	2023	61	Buyer 74	OrdNum 49468	Unique 392020						Buyer 74OrdNum 49468Unique 392020	1
12	2020	17	Buyer 48	OrdNum 10647	Unique 665287						Buyer 48OrdNum 10647Unique 665287	#VALUE!
13	2022	13	Buyer 38	OrdNum 73867	Unique 961025						Buyer 38OrdNum 73867Unique 961025	#VALUE!
14	2024	80	Buyer 34	OrdNum 13682	Unique 626439						Buyer 34OrdNum 13682Unique 626439	#VALUE!
15	2025	89	Buyer 16	OrdNum 47794	Unique 211090						Buyer 16OrdNum 47794Unique 211090	#VALUE!
16	2019	48	Buyer 11	OrdNum 39396	Unique 550797						Buyer 11OrdNum 39396Unique 550797	#VALUE!
17	2025	29	Buyer 73	OrdNum 77354	Unique 213919						Buyer 73OrdNum 77354Unique 213919	#VALUE!
18	2020	78	Buyer 20	OrdNum 30620	Unique 403200						Buyer 20OrdNum 30620Unique 403200	#VALUE!
19	2019	39	Buyer 38	OrdNum 20470	Unique 995111						Buyer 38OrdNum 20470Unique 995111	#VALUE!
20	2021	70	Buyer 80	OrdNum 83981	Unique 478209						Buyer 80OrdNum 83981Unique 478209	#VALUE!
21	2020	51	Buyer 87	OrdNum 89880	Unique 845895						Buyer 87OrdNum 89880Unique 845895	#VALUE!
22	2024	43	Buyer 2	OrdNum 73770	Unique 756503						Buyer 2OrdNum 73770Unique 756503	#VALUE!
23	2025	93	Buyer 70	OrdNum 48773	Unique 922951						Buyer 70OrdNum 48773Unique 922951	#VALUE!
CF (2)

Cell Formulas
Range	Formula
L4:L23	L4	=IF(C1="","",SEARCH($C$1,$K$4:$K$23))
K4:K23	K4	=C4&D4&E4
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A4:J23	Expression	=$L4	text	NO