I am attempting to create a dashboard - for which one of the tabs will aim to filter a list of people based on multiple criteria. For the "front cover" part of this i thought representing the criteria through checkboxes would be helpful, but have not had any inspiration on how i could make this pull in from a raw data tab with different formulas. An example of how i envisage this would look is here (example cover and raw data):
https://docs.google.com/spreadsheets/d/1RzQ5vJYwpqDClqcPe_U7meRdtFBYL_Gn6b8_yKIP1RU/edit#gid=0
The main issue i'm having is that it covers multiple criteria and i'm struggling to come up with a formula to return data with that. So, in the example sheet, i could check "apples" and "mushrooms" and it would return the peoples names from the raw data tab who satisfy this criteria.
I have used google sheets formula and the query function within sheets but am mainly finding it difficult to see how this would be possible.
If anybody had a solution to this, or a similar way they think would achieve the same goal, any help would be much appreciated!
Thanks, Sam
I've updated your spreadsheet, with the formula in Data!F2
=IFERROR(FILTER(A2:D9,
IF(Front!$C2 = TRUE, SEARCH(Front!$B2, $B$2:$B$9), LEN($A$2:$A$9)),
IF(Front!$C3 = TRUE, SEARCH(Front!$B3, $B$2:$B$9), LEN($A$2:$A$9)),
IF(Front!$C4 = TRUE, SEARCH(Front!$B4, $B$2:$B$9), LEN($A$2:$A$9)),
IF(Front!$C6 = TRUE, SEARCH(Front!$B6, $C$2:$C$9), LEN($A$2:$A$9)),
IF(Front!$C7 = TRUE, SEARCH(Front!$B7, $C$2:$C$9), LEN($A$2:$A$9)),
IF(Front!$C8 = TRUE, SEARCH(Front!$B8, $C$2:$C$9), LEN($A$2:$A$9)),
IF(Front!$C10 = TRUE, SEARCH(Front!$B10, $D$2:$D$9), LEN($A$2:$A$9)),
IF(Front!$C11 = TRUE, SEARCH(Front!$B11, $D$2:$D$9), LEN($A$2:$A$9))), "No Values Returned in filter. Please try again.")
This should do what you're looking for.
I've been asked to show how this function works.
To start with the IFERROR this is wrapped around the main function, so if the FILTER returns nothing, the user is not met with an error message.
The bulk of the function is built around FILTER which acts as a proxy for QUERY, which is what @Sam Breddy originally tried.
Working with the checkboxes that evaluate to TRUE or FALSE depending on if they have been checked or not, we can started to create our dynamic filtering function.
The first filter parameter ... IF(Front!$C2 = TRUE, SEARCH(Front!$B2, $B$2:$B$9), LEN($A$2:$A$9) ... determines if there are any Apples in $B$2:$B$9 only if the checkbox in Front!$C2 is set to TRUE.
The trick here is to have a valid value for when the checkbox is FALSE. If the FALSE parameter was set to nothing / blank, an error would pop up:
FILTER has mismatched range sizes. Expected row count: 8. column count: 1. Actual row count: 1, column count: 1.
To counter this, I'm just returning the first column's length back to the function with LEN($A$2:$A$9) which will have no effect on the filter.
The interesting part is the SEARCH which returns:
The position at which a string is first found within text and ignores capitalization of letters. Returns #VALUE! if the string is not found.
Search finds the String you're looking for ... in this case Apple and then passes that string's location back to the FILTER allowing it to only show the rows where it evaluates to to TRUE.
E.g. SEARCH(Front!$B2, $B$2:$B$9) will return 1
By using a SEARCH method for each subsequent parameter for our FILTER function, they act as an OR allowing the function to evaluate for APPLES or ORANGES.
Honestly, I took a stab at this problem, and ended up coming out on top. I don't think it will work for items in the same column that have the same first letter
E.g. If you're looking for Apples and Andy's in the same column ...
I'm also sure there might be some issues with doing this sort of dynamic filtering over two columns with the same values, but this solution should fit your needs.
Cheers,