SOLVED

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

Occasional Contributor

# 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?

Felipe

8 Replies

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

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

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

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

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

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

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

@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

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

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

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

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.

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

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.

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

This one has solved the question. Thanks a lot!!!