CODEWITHSUNDEEP
excelexcel-formula
Excel Question
Excel Question

Reputation: 11

Excel Dynamic Filter (Columns)

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

Upvotes: 1

Views: 55

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

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.

Upvotes: 2

Related Questions