最新消息:雨落星辰是一个专注网站SEO优化、网站SEO诊断、搜索引擎研究、网络营销推广、网站策划运营及站长类的自媒体原创博客

Excel Dynamic Filter (Columns) - Stack Overflow

programmeradmin8浏览0评论

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
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.

发布评论

评论列表(0)

  1. 暂无评论