Search code examples
google-sheetsgoogle-sheets-formula

GOOGLE SHEET- How to get filter function dropdown of data to show all data?

I have a Google sheet spreadsheet where I am pulling data from sheet "INSERT LINKS HERE" to sheet "Data Pull". The problem I am having is in sheet "Data Pull" all the filter drop-down are being too specific. How do I show all data and only have the data be filtered only as more drop downs are clicked?

Right now I have to click all drop-down to get the specific data that I want. For example, I would like all of Branch 1's links to be shown when I click Branch 1 as the dropdown and not have to choose anything from the other drop-down.

This is the formula I am currently using: =ARRAYFORMULA(IF(A2="All Branches",'(INSERT LINKS HERE) '!B2:G3601, filter('(INSERT LINKS HERE) '!A2:G3601,('(INSERT LINKS HERE) '!A2:A3601=A2)('(INSERT LINKS HERE) '!B2:B3601=B2)('(INSERT LINKS HERE) '!C2:C3601=C2)*('(INSERT LINKS HERE) '!D2:D3601=D2))))

Here's the spreadsheet- feel free to edit and explain how I can make this better. Thanks! Google sheet

Solution

  • You may try:

    =query({'(INSERT LINKS HERE) '!A2:G},"where 1=1" 
             &if(len(A2), " AND Col1= '"&A2&"'",)
             &if(len(B2), " AND Col2= '"&B2&"'",)
             &if(len(C2), " AND Col3= '"&C2&"'",)
             &if(len(D2), " AND Col4= '"&D2&"'",),0)

    enter image description here

    Updated Answer:

    =let(Σ,'(INSERT LINKS HERE) '!A2:G,
     filter(Σ,let(Λ,index(Σ,,1),if(len(A2),Λ=A2,(Λ="")+(Λ<>""))),
              let(Λ,index(Σ,,2),if(len(B2),Λ=B2,(Λ="")+(Λ<>""))),
              let(Λ,index(Σ,,3),if(len(C2),Λ=C2,(Λ="")+(Λ<>""))),
              let(Λ,index(Σ,,4),if(len(D2),Λ=D2,(Λ="")+(Λ<>"")))))