Filter formula being stopped by #spill

%3CLINGO-SUB%20id%3D%22lingo-sub-2766963%22%20slang%3D%22en-US%22%3EFilter%20formula%20being%20stopped%20by%20%23spill%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2766963%22%20slang%3D%22en-US%22%3E%3CP%20data-unlink%3D%22true%22%3EHi%2C%20I%20have%20a%20project%20where%20I%20have%20workbook%20of%20data%20in%20one%20work%20book%20and%20in%20a%20new%20workbook%20i%20need%20to%20extract%20lines%20of%20data%20based%20off%20the%20first%20column.%20The%20trouble%20i'm%20having%20is%20that%20rather%20than%20just%20pulling%20through%20the%20first%20line%20of%20data%20i%20need%20it%20to%20pull%20through%20all%20lines%20of%20data%20with%20the%20based%20of%20the%20key%20word%20before%20moving%20on%20to%20the%20next%20line%20of%20data%20and%20so%20on.%20Im%20using%20the%20Filter%20formula%20but%20it%20seems%20to%20stop%20itself%20with%20the%20%23spill%20issue.%20I%20have%20attached%20a%20basic%20copy%20of%20the%20files.%20%22Source%201%22%20will%20be%20the%20main%20database%20and%20Data%20Input%20is%20the%20one%20i%20will%20be%20working%20on.%20On%20sheet%202%20i%20have%20the%20keywords%20of%20the%20data%20I%20want%20to%20returning%20and%20sheet%201%20A1%20is%20where%20I%20want%20it%20returning%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3EThanks%20in%20Advance%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2766963%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-2767152%22%20slang%3D%22en-US%22%3ERe%3A%20Filter%20formula%20being%20stopped%20by%20%23spill%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2767152%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1161046%22%20target%3D%22_blank%22%3E%40CCLiamGoodman%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DFILTER('%5BSource%201.xlsx%5DSheet1'!%24A1%3A%24D14%2CCOUNTIF(Sheet2!B1%3AB4%2C'%5BSource%201.xlsx%5DSheet1'!%24A%241%3A%24A%2414))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EI'd%20do%20not%20recommend%20to%20use%20entire%20columns%20as%20a%20range%2C%20better%20dynamic%20ranges%20or%20even%20better%20tables.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi, I have a project where I have workbook of data in one work book and in a new workbook i need to extract lines of data based off the first column. The trouble i'm having is that rather than just pulling through the first line of data i need it to pull through all lines of data with the based of the key word before moving on to the next line of data and so on. Im using the Filter formula but it seems to stop itself with the #spill issue. I have attached a basic copy of the files. "Source 1" will be the main database and Data Input is the one i will be working on. On sheet 2 i have the keywords of the data I want to returning and sheet 1 A1 is where I want it returning

 

Thanks in Advance

1 Reply

@CCLiamGoodman 

That could be

=FILTER('[Source 1.xlsx]Sheet1'!$A1:$D14,COUNTIF(Sheet2!B1:B4,'[Source 1.xlsx]Sheet1'!$A$1:$A$14))

I'd do not recommend to use entire columns as a range, better dynamic ranges or even better tables.