=FILTER to a set range of cells, avoiding #SPILL! error.

[Marhier]

Good morning everyone.
I've got a small problem I'm hoping someone can help me solve.

I've created a dashboard which has a small table in the top right hand corner, and a table of similar size underneath it
Above these tables is a search box, where a user types a product code, or supplier name and it returns results based on a filter function.

So I understand the reason why I'm getting a #SPILL! error - because the table underneath is in the way of my FILTER results...
I'm wondering what I need to do/how to amend my formula so that it only returns results in the set range - in this case (V8:AA27).

My current formula is as follows:

=IF(LEN(V6)=0,"No Records Found",FILTER(Data[[MStockCode]:[MPriceUom]],ISNUMBER(SEARCH(V6,Data[MStockCode]))+ISNUMBER(SEARCH(V6,Data[Supplier Name]))+ISNUMBER(SEARCH(V6,Data[MPriceUom])),"No Records Found"))

Table looks as follows:

Thinking maybe FILTER would not be the right option for this?

All help is greatly appreciated.
Thank you.

 

[RoryA]

You could wrap the FILTER function in a TAKE function - eg TAKE(FILTER(...),20) to return at most the first 20 results.

 

[Marhier]

You absolute HERO!
I was not aware of this TAKE function.

I can confirm this has solved my problem.

I amended my code to:

=IF(LEN(V6)=0,"No Records Found",TAKE(FILTER(Data[[MStockCode]:[MPriceUom]],ISNUMBER(SEARCH(V6,Data[MStockCode]))+ISNUMBER(SEARCH(V6,Data[Supplier Name]))+ISNUMBER(SEARCH(V6,Data[MPriceUom]))),20))

and it worked a treat.

Can't thank you enough.

 

[RoryA]

I was not aware of this TAKE function.

It's relatively new.

Glad we could help.