I was wondering how we could use the In APP Filter on a column used on a column generated by a GOOGLE FORM CHECKBOX question.
I saw the post about VEGAN options (2/19)
So I want the row to be chosen by any VEGAN option, but for that item, they could choose High Protein without Vegan, etc.
I am using this sheet: https://docs.google.com/spreadsheets/d/1p41USD9J0WC3UlXMoBGOltWkAEwmmhCsuigtzSyJ66k/edit?usp=sharing
I have several columns with several options generated by a Google Form Checklbox list (eg. Topics, Age, Population, Thinking Routines), so to find each combination would be difficult OR if I created several columns like you suggest in the other post, my spreadsheet would have 100 type columns.
Is there away to pick out one item from a cell like “topic” no matter else what is in that cell?
BTW: This is a really cool APP, but I need to solve this problem in order to use it and perhaps purchase in the future.
Thanks for your help.
Well this was an interesting challenge. I created a second sheet that builds out the Title and each selected comma delimited option split out into separate rows. This allowed me to create relation and lookup columns that link the title in the main sheet to the title in the crosswalk sheet. You can assign the Lookup column to the filter (I assigned Topics to the Filter). The formula in the sheet was copied from the web, so I haven’t taken the time to fully understand how it works, but it gave me what I was looking for. You can copy the app. I did scrub the names and email addresses.
So the answer is to create a separate workbook page and the array looks like it is doing the sorting, right?
I am not sure how lkup topics was connected to the spreadsheet. Also, it looks like I can only filter by one column. Not multiple ones.
BTW: I couldn’t get the filter to work in Android mode. There is no DONE button.
Thanks for clarifying.
Yes you need the second sheet so you can build the relation and use the lookup to return an array of values for the in-app filter to work. Basically you need to separate the categories that google forms is joining into a comma delimited list. I’m not sure what you mean by sorting the array. I’m just building the crosswalk sheet in the order that the entries were made in your first sheet. You can sort the list in the tab however you want, but I’m assuming the filter sorts, and makes unique, the values in the order that they are found in lookup column.
The lkup-Topics column is created from the relation column that links the Title from the Plans sheet to the Title in the Crosswalk sheet. The lookup returns all of the individual categories for that Title.
There currently doesn’t appear to be a way to have more than one filter. I set up the relation and lookup columns for all columns, but I chose Topics because that’s the particular one you mentioned. Are you saying that you want all possible selections in one filter? That can probably be done, but you will have several dozen options in the one filter with no way to differentiate which column they are tied to. It would probably require joining all categories in one pair of columns instead of 5 pairs. Then creating one relation column and one lookup column on the Plans sheet. The single lookup column would contain all possible categories that you could filter by.
I didn’t have any problems with the filter on my phone. Instead of Done, I have an X. Just pick your selections for the filter and tap on whatever button shows at the top right of the screen.