Forum Discussion
Solved
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 i...
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.
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.