Forum Discussion

felipeprevente's avatar
felipeprevente
Copper Contributor
Sep 29, 2022

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

 

  • felipeprevente 

     

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

     

     

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

     

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor

    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)
      • Patrick2788's avatar
        Patrick2788
        Silver Contributor
        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.
  • JMB17's avatar
    JMB17
    Bronze Contributor

    felipeprevente 

     

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

     

     

    • felipeprevente's avatar
      felipeprevente
      Copper Contributor

      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
      • JMB17's avatar
        JMB17
        Bronze Contributor
        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.