What I'm trying to do is have a filter be dynamically edited. I want to change the columns included and not include based given cell (or calculation) that determines weather the column should be included or not.

=FILTER(Table1[[#All],[Column1]:[Column5]],{1,0,1,0,1})

----->would include columns 1,3 and 5 I'd like to have it so the "{1,0,1,0,1}" is determined by cell outside of the function or tabulates the columns to be included internally

Also I'm not sure how to do this in VBA and strongly prefer to not use VBA

I have tried the above and using

=FILTER(Table1[[#All],[Column1]:[Column5]],INDIRECT(A5))

where A5 is the proper concat :EX {1,1,0,0,0} but it doesn't work

What I'm trying to do is have a filter be dynamically edited. I want to change the columns included and not include based given cell (or calculation) that determines weather the column should be included or not.

=FILTER(Table1[[#All],[Column1]:[Column5]],{1,0,1,0,1})

----->would include columns 1,3 and 5 I'd like to have it so the "{1,0,1,0,1}" is determined by cell outside of the function or tabulates the columns to be included internally

Also I'm not sure how to do this in VBA and strongly prefer to not use VBA

I have tried the above and using

=FILTER(Table1[[#All],[Column1]:[Column5]],INDIRECT(A5))

where A5 is the proper concat :EX {1,1,0,0,0} but it doesn't work

• excel
• excel-formula

• 3 Have you considered using CHOOSECOLS instead? – P.b Commented Jan 17 at 23:18

1 Answer 1

Put the array in without the {} then use TEXTSPLIT:

=FILTER(Table1[[#All],[Column1]:[Column5]],--TEXTSPLIT(A5,","))

INDIRECT Takes a string range reference and turns it into an actual range reference. It does not turn a string into an array, nor will it turn a formula string into a formula.