AOB
Well-known Member
- Joined
- Dec 15, 2010
- Messages
- 660
- Office Version
- 365
- 2016
- 2013
- Platform
- Windows
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 :
Next I'm wrapping that inside a second FILTER to include/exclude columns based on their index/position in the table :
All working beautifully, as expected
Now - what I'm trying to do is have that include/exclude array - the
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!
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 :
Excel Formula:
=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 :
Excel Formula:
=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!