Filter function for Google Sheets - dynamic cell reference

Question

this is driving me crazy!

I need to make a FILTER function in GS dynamic to return headers and row data from a table, but ignoring any blank columns. In the table, people will have different columns with data in.

I can build the FILTER expression, but can't make it dynamic. It looks like FILTER needs absolute cells, and won't accept references from other cells (see attached sheet). Please help my sanity! Many thanx

https://docs.google.com/spreadsheets/d/1pI9UT_LudmVxo9LeUfIi-03gQ939hlsOMke4hQFUVRQ/edit?usp=sharing

Answer 1

In cell B3, change your formula

From:

=FILTER({$7:$7;10:10},NOT(ISBLANK(10:10)))

To:

=FILTER({A7:J7;INDIRECT(C15)},NOT(ISBLANK(INDIRECT(C15))))

and in cell C15, change your formula

From:

=MATCH(B15,A8:A13,0)+7 &":"& MATCH(B15,A8:A13,0)+7

To:

="A"&MATCH(B15,A8:A13,0)+7 &":J"& MATCH(B15,A8:A13,0)+7

Output:

Reference:

• INDIRECT()

Answer 2

you could use query function

=QUERY({A7:J13},"SELECT * WHERE Col1='"&B15&"'",1)

check Copy of Sheet1 sheet