Jan 12 2021 04:09 PM
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))
Jan 12 2021 04:29 PM
Jan 13 2021 02:35 AM
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))
)
Jan 13 2021 05:42 AM
Jan 13 2021 05:43 AM
Jan 13 2021 05:44 AM
Thank you for your help!
I ended up restructuring the landing page to allow for up to the entire 100 possible rows of data. This solution actually worked better as I realized the landing page needed all that data to allocate other expenses against. Thanks again!
Jan 13 2021 06:05 AM
@Cohutta68 , you are welcome