Forum Discussion
Solved
Filter data - then filter the filtered data
I have some data that is over 3200 lines with multiple columns each I know I can just select the headers and put a filter in each column - and can drill down the data But - I want to create ...
See attached sample file where a tab named List is hidden. Lists on that sheet are dynamically adjusted according the filter(s)/criteria already applied on the Query tab:
With Data formatted as a structured Table, FILTER formula on the Query tab is:
=LET( Filtered, FILTER(Data, IF(ISBLANK(B4), 1, Data[Application] = B4) * IF(ISBLANK(C4), 1, Data[Type] = C4) * IF(ISBLANK(D4), 1, Data[Series] = D4) * IF(ISBLANK(E4), 1, Data[Size] = E4) * IF(ISBLANK(F4), 1, Data[Geometry] = F4) ), IF(QueryCriterias, Filtered, "Select at least a criterion") )Where QueryCriterias in defined in Name Manager as: =COUNTA(Query!$B$4:$F$4)
True, 3200 lines is not a lot - and I never said it was - I was only stating that is what I am working with - and that it is too much data to scroll through
I want a query page to make it clean and easy to view the data using pre-determined filters - as this document will be given to Sales people and distributors to use as a reference - so I am going to hide the original data tab....and other tabs - so they cannot be messed with
Basically this is a products data file - so I have made the filters change based on the type of information the user is looking for
It is my hope that I can use the first filter - and have the data populate
Then if I wish to use the second filter - it will then filter the already filtered data further......and so on if I wish to use any of the other filters
That way I can see as much or as little of the data as I want
As it stands right now - the data will only populate once I have met all the criteria in the formula
Hopefully that helps answer your questions
I have never used the IFS function.....can it be used in the filter equation?
I want a query page to make it clean and easy to view the data using pre-determined filters - as this document will be given to Sales people and distributors to use as a reference - so I am going to hide the original data tab....and other tabs - so they cannot be messed with
Basically this is a products data file - so I have made the filters change based on the type of information the user is looking for
It is my hope that I can use the first filter - and have the data populate
Then if I wish to use the second filter - it will then filter the already filtered data further......and so on if I wish to use any of the other filters
That way I can see as much or as little of the data as I want
As it stands right now - the data will only populate once I have met all the criteria in the formula
Hopefully that helps answer your questions
I have never used the IFS function.....can it be used in the filter equation?
See attached sample file where a tab named List is hidden. Lists on that sheet are dynamically adjusted according the filter(s)/criteria already applied on the Query tab:
With Data formatted as a structured Table, FILTER formula on the Query tab is:
=LET(
Filtered, FILTER(Data,
IF(ISBLANK(B4), 1, Data[Application] = B4) *
IF(ISBLANK(C4), 1, Data[Type] = C4) *
IF(ISBLANK(D4), 1, Data[Series] = D4) *
IF(ISBLANK(E4), 1, Data[Size] = E4) *
IF(ISBLANK(F4), 1, Data[Geometry] = F4)
),
IF(QueryCriterias, Filtered, "Select at least a criterion")
)
Where QueryCriterias in defined in Name Manager as: =COUNTA(Query!$B$4:$F$4)
- Thank you so much - this worked!
Of course I had to modify some of the names to reflect my own spread sheet - but this is what the final formula worked out to be
=LET(
Filtered, FILTER(Data,
IF(ISBLANK(B4), 1, Application = B4) *
IF(ISBLANK(C4), 1, Type = C4) *
IF(ISBLANK(D4), 1, Series = D4) *
IF(ISBLANK(E4), 1, Size = E4) *
IF(ISBLANK(F4), 1, Geometry = F4) * IF(ISBLANK(G4), 1, Grade = G4)
),
IF(QueryCriterias, Filtered, "SELECT APPLICATION")
)
Thanks so much again - its doing exactly what I wantedYou're sgeisel1975430 & Thanks for providing feedback