Nov 04 2021 06:58 PM
Hi,
I want to auto-fill a cell if a word exists anywhere in a row. I have used the following formula IF(ISNUMBER(SEARCH("Please",D2:Q2)),"Approve","No qualify"). But I keep getting the spill error.
Would really appreciate the help!
Nov 04 2021 07:28 PM
You are giving SEARCH() a range instead of a single cell reference. So it spills. And if there is not enough free space you get the #SPILL! error.
Untested.
=IF(COUNTIFS(D2:Q2,"Please"),"Approve","No qualifiy")
Nov 04 2021 08:28 PM
Thanks @Detlef Lewin
The auto-fill is not working on the condition. I think it's because each cell value in the row range is selected from a drop-down list? The auto-fill defaults to "No qualify" regardless of whether "Please" is there or not in the range.
Nov 04 2021 08:33 PM
Nov 04 2021 11:58 PM
Solution@charmy_23 Try this:
=IF(SUMPRODUCT(IFERROR(SEARCH("Please",D2:Q2),0))<>0,"Approved","Not qualified")
Nov 12 2021 04:38 PM
Nov 04 2021 11:58 PM
Solution@charmy_23 Try this:
=IF(SUMPRODUCT(IFERROR(SEARCH("Please",D2:Q2),0))<>0,"Approved","Not qualified")