SOLVED

Auto-fill dependent on cell range

%3CLINGO-SUB%20id%3D%22lingo-sub-2929644%22%20slang%3D%22en-US%22%3EAuto-fill%20dependent%20on%20cell%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2929644%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20auto-fill%20a%20cell%20if%20a%20word%20exists%20anywhere%20in%20a%20row.%20I%20have%20used%20the%20following%20formula%20IF(ISNUMBER(SEARCH(%22Please%22%2CD2%3AQ2))%2C%22Approve%22%2C%22No%20qualify%22).%20But%20I%20keep%20getting%20the%20spill%20error.%26nbsp%3B%3C%2FP%3E%3CP%3EWould%20really%20appreciate%20the%20help!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2929644%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2929688%22%20slang%3D%22en-US%22%3ERe%3A%20Auto-fill%20dependent%20on%20cell%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2929688%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1207761%22%20target%3D%22_blank%22%3E%40charmy_23%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20are%20giving%20SEARCH()%20a%20range%20instead%20of%20a%20single%20cell%20reference.%20So%20it%20spills.%20And%20if%20there%20is%20not%20enough%20free%20space%20you%20get%20the%20%23SPILL!%20error.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUntested.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DIF(COUNTIFS(D2%3AQ2%2C%22Please%22)%2C%22Approve%22%2C%22No%20qualifiy%22)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2929774%22%20slang%3D%22en-US%22%3ERe%3A%20Auto-fill%20dependent%20on%20cell%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2929774%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20auto-fill%20is%20not%20working%20on%20the%20condition.%20I%20think%20it's%20because%20each%20cell%20value%20in%20the%20row%20range%20is%20selected%20from%20a%20drop-down%20list%3F%20The%20auto-fill%20defaults%20to%20%22No%20qualify%22%20regardless%20of%20whether%20%22Please%22%20is%20there%20or%20not%20in%20the%20range.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2929787%22%20slang%3D%22en-US%22%3ERe%3A%20Auto-fill%20dependent%20on%20cell%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2929787%22%20slang%3D%22en-US%22%3EThen%20there%20is%20no%20exact%20match.%20Maybe%20additional%20spaces%3F%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
New 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 (New 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.