I’ve mostly worked with Excel before but I’m having to achieve what I’m trying to do with Apple’s Numbers application.
I have a spreadsheet full of values in column A on a table called Tracker.
I would like a separate table, Filter Criteria, which contains 1 column where I can paste a bunch of values in multiple cells and then I can activate a filter which will show me all of the cells in the Tracker table that are listed in Filter criteria table.
https://i.stack.imgur.com/e3ZW0.png
I would love to be able to do this with the built in Filter feature located in the sidebar on the right of the Numbers UI, however it seems to only let you type in text rather than referencing a cells or column of cells. Because my filter criteria could be quite long, it would take a long time to set up individual filters for each criteria value.
It would be great if someone could point me in the direction on how to achieve this.
Just to be clear, the ‘expected filtered result’ shouldn’t be a new table, it should be the first table but simply filtered to only show the matching results.
—–
Sam
To do this, create a new column in the Tracker table. Then use a LOOKUP to see if the Car color is in the filter list. If it is, then you'll get the match, and an error otherwise. So surround it with ISERROR to convert that to true and false.
ISERROR(LOOKUP($A2,Critieria::A))
Then just set the filter for the Tracker table to look for the text "false" in that new column. Now as you add things to the Criteria list, the results of this new column will change and the filter will only show the "false" results that match the filter.
Hi Gary
Thanks for your fast response. Apologies if I have misinterpreted your solution but when I try to apply it then I get 'FALSE' for every cell.
https://i.imgur.com/0rS46WY.png
I expected the first 3 cells (Blue, Red, Blue) to show FALSE but I expected the 4th cell (Green) to show something other than false, such as an error. What am I doing wrong here?
Sam: Post the actual example Numbers document so I can look at it, not an image.