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?
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Complex Data Validation
0
Answers
1
Selected Answer
Lisbeth
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):
=ISNA(MATCH(A2,$D$2:$D$20,0))=TRUEIt 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:
=ISNA(MATCH(A2,Excluded,0))=TRUEDiscussion
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)
Note that the list of excluded names could be on another sheet (so out of the way)
John_Ru (rep: 6142) 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: 6142) 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: 6142) Jul 20, '21 at 5:20 pm
0
<a href="https://www.vatcalculator.info/pl/">Check out</a> how to check Vat Rate using site.