admin管理员组

文章数量:1320799

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
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
Add a comment  | 

1 Answer 1

Reset to default 2

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.

本文标签： Excel Dynamic Filter (Columns)Stack Overflow

版权声明：本文标题：Excel Dynamic Filter (Columns) - Stack Overflow 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.betaflare.com/web/1742089900a2420200.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。