help with a search function

[andysacko]

Hi
I have been trying all morning but just cant figure it out, I am try to make a search box in my main entry form sheet that searches for data thats been entered into a seperate data sheet
this is what i have done so far but i keep getting errors in formula
selected the cell in entry form sheet that i want to type the search txt into, selected conditional formating, new formating rule, use a formula to determine which cells to format and in the rule description i have selected the range from the data sheet b3 to m200

=SEARCH(Data!$B$3:$M$200)

but it dont work, could anybody help me with this or a differant way to do it i am using excel 2016
thanks

 

[Cubist]

Try:
Highlight B3:M200 -> Conditional formatting by formula.

Book3
	A	B	C	D	E	F
1	Search:	sn
2
3		Kite	Apple	Tiger	Cloud	River
4		Snake	Pizza	Lemon	Sunset	Grass
5		Moon	Book	Pencil	Ocean	Tree
6		Dance	Bridge	Snow	Field	Sneaker
7		Flame	Rocket	Dream	Light	Wave
Sheet5

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:F7	Expression	=SEARCH($B$1,B3)	text	NO

 

[andysacko]

Try:
Highlight B3:M200 -> Conditional formatting by formula.

Book3
	A	B	C	D	E	F
1	Search:	sn
2
3		Kite	Apple	Tiger	Cloud	River
4		Snake	Pizza	Lemon	Sunset	Grass
5		Moon	Book	Pencil	Ocean	Tree
6		Dance	Bridge	Snow	Field	Sneaker
7		Flame	Rocket	Dream	Light	Wave
Sheet5

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:F7	Expression	=SEARCH($B$1,B3)	text	NO

i cant find Conditional formatting by formula. in the conditional formating option box

 

[Cubist]

What are the options you have?

 

[andysacko]

What are the options you have?

 

[Cubist]

It's the last option: Use a formula to determine which cells to format.

 

[andysacko]

It's the last option: Use a formula to determine which cells to format.

still no joy ime doing something wrong

 

[Skyybot]

@andysacko , it's because you've entered too few arguments. The function requires 3. ex. - =SEARCH(what_to_search_for , area_to_search , start_cell)

 

[Cubist]

still no joy ime doing something wrong
View attachment 119940

Look at my formula in post #2 again.