Forum Discussion
FILTER Function using a cell reference as a Boolean for the Include parameter
Hello Experts,
I've created a concatenated field to retrieve the boolean expression to be used in a FILTER function as below:
A1 = "{"&B1&","&C1&","&"}" OR {1,0,1}
A2 = 1
So, my formula is: FILTER(FILTER(GroupAuth!D:F,GroupAuth!A:A=A2),A1) OR, Filter/Retrieve the columns from D to F, where the value at the row level in column A = 1, retrieving only the first and third columns.
Is it possible to use a cell reference instead of writing the boolean in the "Include" parameter? If it's not, does anybody knows a workaround?
Thanks in advance!
Felipe
I usually just include a field above my table containing TRUE/FALSE to indicate which columns to include:
=FILTER(FILTER(D5:F20;(A5:A20=A2));D4:F4=D1:F1)Maybe like this. In cells D1, E1 and F1 you can enter "D", "E" or "F" in order to dynamically return the chosen columns in the spilled range.
You might use:
=FILTER(CHOOSE({1,3},D1:D10,0,F1:F10),A1:A10=1)or even better, if you have CHOOSECOLS:
=CHOOSECOLS(FILTER(D1:F10,A1:A10=1),1,3)Patrick2788 , can this be dynamic? I mean, these 1,3 parameters in CHOOSECOLS.
Thanks for your reply!
- Yes, it can. It all depends on your goal. If you have a sample workbook, a formula can be drawn up. You'd likely get a few solutions from the community here.
I usually just include a field above my table containing TRUE/FALSE to indicate which columns to include:
JMB17 , that's my goal!
I'll try your solution then I tell you guys. Many thanks!
Just to explain what I've done:
I written: =IF(E3<>"",1,0) - So, when a field is filled, returns 1, otherwise, 0.
Then, I concatenate the array to produce the {1,0,1,1,1,1,1,1} and reference it later in the Include parameter. It seems that the formula doesn't accept this indirect reference
1 0 1 1 1 1 1 1 Various Authorisations Full Authorisation Various Authorisations Various Authorisations Various Authorisations Various Authorisations Various Authorisations Various Authorisations Full Authorisation Full Authorisation No Authorisation No Authorisation No Authorisation No Authorisation Full Authorisation - I don't understand why you're trying to do it indirectly by concatenating an array constant to pass to the filter function instead of just referencing the range directly in the filter function?
The issue is that the concatenated array is not actually an array as far as Excel is concerned, it's just text that means nothing to the filter function. You might be able to use an old Excel 4 macro function in a lambda to convert it. In the name manager, define a new name (call it EVAL) and refers to =lambda(expression, evaluate(expression))
Then, try =Filter(range, eval(A1)) where A1 is the text array constant. But, if you have to share the file with other users, you might test on their machines as I think there may be some security settings that could block Excel 4 macros.
- This one has solved the question. Thanks a lot!!!
