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
Share Improve this question edited Jan 18 at 15:48 Rohit Gupta 4,18522 gold badges34 silver badges46 bronze badges asked Jan 17 at 21:54 Excel QuestionExcel Question 111 bronze badge 1- 3 Have you considered using CHOOSECOLS instead? – P.b Commented Jan 17 at 23:18
1 Answer
Reset to default 2Put 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.