Apply a dynamic include/exclude in outer FILTER formula to dynamically determine which columns to return

[AOB]

I'm playing around with the FILTER formula and trying to see if I can get something to work...

So I've set up an initial table and then have a formula that shows a filtered view of that table based on some condition :

=IFERROR(FILTER(tblSomeTable,tblSomeTable[SomeField]>0,""),"No Items")

Next I'm wrapping that inside a second FILTER to include/exclude columns based on their index/position in the table :

=IFERROR(FILTER(FILTER(tblSomeTable,tblSomeTable[SomeField]>0,""),{1,0,1,1,1,0}),"No Items")

All working beautifully, as expected

Now - what I'm trying to do is have that include/exclude array - the {1,0,1,1,1,0} part of the outer FILTER - applied dynamically.

In other words, if I have that sequence in a cell somewhere (named range, whatever) - can I refer to that cell, and apply its value into the outer FILTER formula, rather than "hard-coding" it into the formula?

I already have a cell with the array formatted exactly the way it needs to appear within the formula - I just don't know how to refer to it?

Am thinking there must be a way á la INDIRECT or something along those lines?

Thanks!

 

[RoryA]

I doubt that is the best setup but you could put the comma separated list of numbers (1,0,1,1,1,0) into a cell, say J1, and then use TEXTSPLIT(J1,",")+0 as the array

 

[AOB]

I doubt that is the best setup but you could put the comma separated list of numbers (1,0,1,1,1,0) into a cell, say J1, and then use TEXTSPLIT(J1,",")+0 as the array

LOL - yeah, I totally appreciate your concern, I am really just toying around with the functionality to see what it can do

That (of course) works perfectly, thank you so much!

(Might I ask the significance of the "+0" at the end?...)

 

[RoryA]

Might I ask the significance of the "+0" at the end?

TEXTSPLIT always returns text, but you need numbers, so adding 0 coerces the "1" and "0" to actual 1 and 0

 

[AOB]

TEXTSPLIT always returns text, but you need numbers, so adding 0 coerces the "1" and "0" to actual 1 and 0

Ahhh, of course, yes indeed - excellent, thanks for explaining!