Complex Data Validation


I'm not sure if what I want to do can actually be done. I have a column where participant names are entered but I want to use data validation to allow any value except the names of specific individuals (people who have been banned). Is there a custom formula I can use for this?



Selected Answer


For cell A2 say and excluded names in D2 to D20, this Custom data validation formula will prevent those names being entered (and you can copy it down your column):


It works since if there's a match with the typed name, MATCH produces a number so the surrounding ISNA is FALSE but for any other name, MATCH gives #N/A so ISNA is TRUE (and the value is permitted). 

Better still, create an named range called Excluded say, and the formula becomes:



Thanks so much for your reply. So you are saying that I would have to create a column for the excluded names and the formula would refrence that column for the column where I actually enter all names? Let me give this a try and I'll report back.
Lisbeth (rep: 2) Jul 6, '21 at 9:15 am
The first formula worked, thank you.
Lisbeth (rep: 2) Jul 6, '21 at 9:53 am
Thanks for selecting my answer Lisbeth 

Note that the list of excluded names could be on another sheet (so out of the way)
John_Ru (rep: 1917) Jul 6, '21 at 10:45 am
BTW Lisbeth... I did a lot of work on New Year's Day 2021 on your previous question Emailing from Excel using VBA code but was disappointed that you neither selected my Answer nor replied to say if it worked or not for you. Did it?
John_Ru (rep: 1917) Jul 6, '21 at 12:10 pm
Yes and no. It work to an extent but not in the way I intended. I ended up having to rethink my approch to the problem completly and ended up downloading a 3rd party software called autofollow up. It gives me more control. I think your solution cold have worked minus a few kinks. I would mind revisiting it in the future but gave up after spending way too much time trying to figure it out. 
Lisbeth (rep: 2) Jul 20, '21 at 2:50 pm
Thanks Lisbeth, at least I know albeit several months after my efforts. 
John_Ru (rep: 1917) Jul 20, '21 at 5:20 pm
Add to Discussion

<a href="">Check out</a> how to check Vat Rate using site.


Answer the Question

You must create an account to use the forum. Create an Account or Login