Text Search

[ADUNCAN]

Hello,

I'm trying to write a formula which identifies if a specific word is found within a sheet.
I have a cover sheet which needs to be able to identify if the work "REPLENISH" or "REPLACE is present in each of the other sheets within the document and return a "YES" or "NO". Is this possible? Most formulas I've found have only been able to search an individual cell rather than an area on the sheet or an entire sheet.

Thanks in advance.

 

[Automatrix]

Like this.

Book1
	A	B	C	D	E	F	G
1
2	Is "REPLENISH" there?		REPLENISH
3	Yes
4					REPLACE
5	Is "REPLACE" there?
6	Yes
7
8
Sheet4

Cell Formulas
Range	Formula
A3	A3	=IF(COUNTIFS($C$1:$G$7,"*"&"REPLENISH"&"*")>0,"Yes","No")
A6	A6	=IF(COUNTIFS($C$1:$G$7,"*"&"REPLACE"&"*")>0,"Yes","No")

 

[ADUNCAN]

Thank you!

Is there a way for the two words to be searched in the same formula, or do they have to remain separate?

Thanks!

 

[Automatrix]

If just one of the words needs to be there, you can do this:

=IF(OR(COUNTIFS($C$1:$G$7,"*"&"REPLENISH"&"*")>0,COUNTIFS($C$1:$G$7,"*"&"REPLACE"&"*")>0),"Yes","No")

Or this:

=IF((COUNTIFS($C$1:$G$7,"*"&"REPLENISH"&"*")+COUNTIFS($C$1:$G$7,"*"&"REPLACE"&"*"))>0,"Yes","No")

 

[JEC]

You can do this in one Countifs..
Use place the two words between curly brackets.
“*”&{…,…}&”*”

 

[ADUNCAN]

Thanks! That worked!