SOLVED

FILTER Function using a cell reference as a Boolean for the Include parameter

Occasional Contributor

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

 

8 Replies

@felipeprevente 

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

filter.JPG 

@felipeprevente 

 

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)
best response confirmed by felipeprevente (Occasional Contributor)
Solution

@felipeprevente 

 

I usually just include a field above my table containing TRUE/FALSE to indicate which columns to include:

 

JMB17_0-1664472063338.png

 

@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

 

10111111
Various AuthorisationsFull AuthorisationVarious AuthorisationsVarious AuthorisationsVarious AuthorisationsVarious AuthorisationsVarious AuthorisationsVarious Authorisations
Full Authorisation Full AuthorisationNo AuthorisationNo AuthorisationNo AuthorisationNo AuthorisationFull Authorisation

@Patrick2788 , can this be dynamic? I mean, these 1,3 parameters in CHOOSECOLS.

 

Thanks for your reply!

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.
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.
This one has solved the question. Thanks a lot!!!