0

I am trying to filter data from multiple sheets using the newly introduced FILTER function. I have saved a named range for sheet names to use as an Array. This is the formula I am trying to yse but it gives me #Value error. But it works if I use for a single sheet. aharu is the namedrange array for Sheet Names.

=FILTER(INDIRECT("'"&aharu&"'!$B$2:$B$1200"),(INDIRECT("'"&aharu&"'!$L$2:$L$1200")="")*(INDIRECT("'"&aharu&"'!$J$2:$J$1200")<=EDATE(TODAY(),-6)),"No data")

How to use filter function for multiple sheets?

braX
  • 11,506
  • 5
  • 20
  • 33
Keto
  • 21
  • 1
  • 5
  • 1
    FILTER function does not take multiple ranges regardless of wether they are on the same sheet or on multiple sheets. You would have to "simulate" a merged range and then Filter on that "range", look here https://stackoverflow.com/a/69861437/16578424: it's the same question but with UNION instead of FILTER – Ike Jan 12 '22 at 07:38
  • If you create a resource worksheet with the required multiple ranges side by side, then the answer should be similar to the Q&A here: https://stackoverflow.com/q/70616932/3688861 – Tragamor Jan 12 '22 at 14:30

1 Answers1

0

To filter by using the FILTER function in Excel, follow these steps:

Type =FILTER( to begin your filter formula

Type the address for the range of cells that contains the data that you want to filter, such as B1:C50

Type a comma, and then type the condition for the filter, such as C3:C50>3 (To set a condition, first type the address of the "criteria column" such as B1:B, then type an operator symbol such as greater than (>), and then type the criteria, such as the number 3.

Type a closing parenthesis and then press enter on the keyboard. Your entire formula will look like this: =FILTER(B1:C50,C1:C50>3)

Here are the Excel Filters formulas:

  1. Filter by a number

    =FILTER(A3:B12, B3:B12>0.7)

  2. Filter by a cell value

    =FILTER(A3:B12, B3:B12<F1)

  3. Filter by a text string

    =FILTER(A3:B12, B3:B12="Late")

  4. Filter where NOT equal to

    =FILTER(A3:E1000, B3:B1000<>"Bob")

  5. Filter by date

    =FILTER(A3:C12,C3:C12<G1)   (Date entered in cell G1)
=FILTER(A3:C12,C3:C12<DATE(2019,6,1))

  6. Filter by multiple conditions

    =FILTER(A3:C12,(B3:B12="Late")*(C3:C12="Active"))   (AND logic)
=FILTER(A3:C12, (B3:B12="Late")+(C3:C12="Active"))   (OR logic)

  7. Filter from another sheet

    =FILTER('Sheet Name'!A3:B12,'Sheet Name'!B3:B12="Full Time")

  8. The Excel FILTER function:

    =FILTER(A3:B12, B3:B12=F1)
Ruli
  • 2,592
  • 12
  • 30
  • 40
Rutvik
  • 1
  • 1