Suppose I have some names in cells
B1:B3, and a full list of all the names in
A1:A4, is it possible for me to generate a list of names in
A1:A4 that don't appear in
A1:A4 have "A", "B", "C", "D", if
B1:B3 contain "A", "C", "D", then I want to get "B".
I looked through the function list but couldn't figure out how to do it.
Update: With Lance's help, the formula I came up with was:
FILTER(A1:A4, A1:A4<>B1, A1:A4<>B2, A1:A4<>B3)
This gives the right result, however I still have an issue. The problem is that my cells
B1:Bx can expand. I'm managing a sport team and using a Google Docs form for people to submit, they select their name from a picklist and choose a radio button "Yes" (I can play), or "No" (I'm out). What I want to achieve here is to display in my spreadsheet those people who haven't responded. The goal is to update this list automatically after each new submission. Unfortunately when new rows are filled in the spreadsheet, the formula would be screwed up. I'm not sure if there is a solution for this.
Update 2: I found a work around. I copied the cells from
B1:Bx to another region
C1:Cx first, then used the above formula but against
C1:Cx. Now even
B1:Bx expands, the formula is still valid.