Excel OFFSET and FILTER combo formula error-CodePudding

I am wondering why I cannot use OFFSET with FILTER as it returns a #VALUE error. I when I ran the portion of the formula with OFFSET with a parameter, how come it returns as True or False rather than returning a list of numbers that I need. Any suggestions with directing my equation will help!

I had a specific range of an array to filter out, but as soon as the filtering is viable then using OFFSET on the entire array to look for adjacent non-blank values is next.

=FILTER( INDIRECT($A$6&"!$B$5:$Q$34") , 
( OFFSET( INDIRECT( $A$6&"!$C$5" ), 0, 0, COUNTA( INDIRECT( $A$6&"!$C:$C" )) - 5 ) >= $A$9) *
( OFFSET( INDIRECT( $A$6&"!$C$5" ), 0, 0, COUNTA( INDIRECT( $A$6&"!$C:$C" )) - 5 ) <= $A$10) * 
( OFFSET( INDIRECT( $A$6&"!$D$5" ), 0, 0, COUNTA( INDIRECT( $A$6&"!$D:$D" )) - 5 ) >= $A$13) * 
( OFFSET( INDIRECT( $A$6&"!$D$5" ), 0, 0, COUNTA( INDIRECT( $A$6&"!$D:$D" )) - 5 ) <= $A$14) * 
( OFFSET( INDIRECT( $A$6&"!$I$5" ), 0, 0, COUNTA( INDIRECT( $A$6&"!$I:$I" )) - 5 ) >= $A$17) * 
( OFFSET( INDIRECT( $A$6&"!$I$5" ), 0, 0, COUNTA( INDIRECT( $A$6&"!$I:$I" )) - 5 ) <= $A$18), 
 "None")

CodePudding user response：

As @JosWoolley stated, the number of rows is probably not equal. To ensure all the same number of rows you should do the COUNTA all on the same column. And you can leverage LET to do that once:

=LET(
    rw, COUNTA(INDIRECT($A$6&"!B:B")),
    FILTER(INDIRECT($A$6&"!B5:Q" & rw),
          (INDIRECT($A$6&"!C5:C" & rw) >= $A$9) *
          (INDIRECT($A$6&"!C5:C" & rw) <= $A$10) *
          (INDIRECT($A$6&"!D5:D" & rw) >= $A$13) *
          (INDIRECT($A$6&"!D5:D" & rw) <= $A$14) *
          (INDIRECT($A$6&"!I5:I" & rw) >= $A$17) *
          (INDIRECT($A$6&"!I5:I" & rw) <= $A$18),
          "NONE"))