Filter table data & limit returned to 50 rows

Hello,

I am using this formula to filter data from a table.

=IF(B2="","",FILTER(FILTER(TData,IF(B2="All",1,TData[EMPLOYEE NAME]=B2)*(TData[AUDIT DATE]>=C3)*(TData[AUDIT DATE]<=C4)*IF(E3="All",1,TData[SHIFT]=E3)*IF(E4="All",1,TData[TYPE]=E4)*IF(E2="All",1,TData[REASON]=E2)),{1,1,1,1,0,1,1}))

I need to limit the number rows it returns to 50 so it doesn't run into another area and cause a #SPILL! error.

I was researching online that Sequence(50) added to the formula may work, but I can't get that to work.

I appreciate any assistance.

Thank you,
Nick

How about

Formula:

Please Login or Register  to view this content.

Hi Fluff,

With your suggestion I am getting #REF! errors when there is less than 50.

I attached an example. If you know a solution, is there a way that if there is no data found to have it just be blank?

I appreciate any help.

Nick

Awesome! Thank you.

You're welcome & thanks for the feedback.

Hi Fluff,

I like that INDEX better than the additional filter formula, easier to type in 1,2,3.. instead of 1's and 0's lol. In order to use the index in the original formula without a row limit, do I need to have it at the beginning such as the Filter is below?

=IF(B2="","",FILTER(FILTER(TData,IF(B2="All",1,TData[EMPLOYEE NAME]=B2)*(TData[AUDIT DATE]>=C3)*(TData[AUDIT DATE]<=C4)*IF(E3="All",1,TData[SHIFT]=E3)*IF(E4="All",1,TData[TYPE]=E4)*IF(E2="All",1,TData[REASON]=E2)),{1,1,1,1,0,1,1}))

Thank you,

Yes, but you would also need to give it a sequence like

Formula:

Please Login or Register  to view this content.

Cool Thanks!

My pleasure.