Help limiting length results of results from Filter Formula without #REF!

Question

Hey, I am building a new Excel (365) app to track our operations.

On the job specific pages, I am trying to return (via Filter Formula) just the first 'up to 10 rows" of data from another range. Jobs will differ from 1 row to 50 rows, thus I am limiting this summary to the first 10 only. And I have allowed space for a 'Spill' of up to the first 10 relevant rows.

The following formula works well except in the cases that there are less than 10 rows of data in the filtered results. In those cases it returns a #REF!

So if for a given job, there are only 7 rows of data returned to this summary, then rows 8,9 & 10 will show #REF! The #REF! then causes other errors as well.

Any solution ideas?

Here is my filter formula currently;

=INDEX(FILTER('WO Labor Index'!$A$3:$A$10000,'WO Labor Index'!$B$3:$B$10000=A2),SEQUENCE(10))

jmb17 · Answer

I don't have the newest functions to test this, but could you replace 10 with MIN(10, countif()) where countif returns the number of items matching your condition?

Or, could you wrap the index function with iferror and return either blank or zero?

sergeibaklan · Answer

Cohutta68&nbsp;
If FILTER() returns less than 10 rows, INDEX returns REF since you try to reference not existing rows. Variant could be
=LET(
 filt, FILTER('WO Labor Index'!$A$3:$A$10000,
              'WO Labor Index'!$B$3:$B$10000=A2),
 n, ROWS(filt), INDEX(filt,SEQUENCE(n))
)

cohutta68 · Answer

Thank You!!! I got this resolved.

cohutta68 · Answer

Thank You Sergei!!! I got this resolved.

cohutta68 · Answer

Thank you for your help!I ended up restructuring the landing page to allow for up to the entire 100 possible rows of data.&nbsp; This solution actually worked better as I realized the landing page needed all that data to allocate other expenses against.&nbsp; Thanks again!Cohutta68&nbsp;

Forum Discussion

Help limiting length results of results from Filter Formula without #REF!

6 Replies

Resources