SOLVED

Auto-fill dependent on cell range

Copper Contributor

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! 

5 Replies

@charmy_23 

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")

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. 

 

Then there is no exact match. Maybe additional spaces?
best response confirmed by charmy_23 (Copper Contributor)
Solution

@charmy_23 Try this:

=IF(SUMPRODUCT(IFERROR(SEARCH("Please",D2:Q2),0))<>0,"Approved","Not qualified")

@Riny_van_Eekelen 

 

Thank you! That helped! Much appreciated. 

1 best response

Accepted Solutions
best response confirmed by charmy_23 (Copper Contributor)
Solution

@charmy_23 Try this:

=IF(SUMPRODUCT(IFERROR(SEARCH("Please",D2:Q2),0))<>0,"Approved","Not qualified")

View solution in original post