View Full Version : Solved: Data Validation: Unique Names
johnske
07-22-2006, 02:55 PM
I have two columns with the headers 'First Name' and 'Surname'. I want to apply (standard) data validation so that the concatenation of First Name & " " & Surname is unique. i.e. If the name has already been entered it cannot be entered again. What formula would I need to use?
TIA,
John :)
lucas
07-22-2006, 03:22 PM
John, this works for 1 column....
=COUNTIF($A$2:$A$20,A2)=1
johnske
07-22-2006, 07:31 PM
Thanx Steve,
I got that to work OK, but the syntax for the formula concatenation's got me beat :)
Regards,
John
lucas
07-22-2006, 08:33 PM
If you can do the concatenation first couldn't you extract a unique list from that?
matthewspatrick
07-22-2006, 09:18 PM
John,
Following Steve's suggestion, I made a third column that concatenated the first two, and then used this custom validation on the surname column:
=COUNTIF(C:C,A2&" "&B2)=1
It appeared to work...
johnske
07-23-2006, 12:12 AM
Hi Patrick,
Yes, I got that to work, but it doesn't seem very elegant and it also raises the problem - what about users (such as myself) that take the shortcut of selecting entire rows to clear the contents of a few cells? - (the formula you're using for the validation is then cleared)
Regards,
John
matthewspatrick
07-23-2006, 03:45 AM
John,
Data Validation is fallible on many counts. You described something that would wipe it out here; it also will not prevent a copy/paste.
It's sounding like a Worksheet_Change sub is what you're looking for...
johnske
07-23-2006, 05:41 AM
OK, thanx guys,
I can work around this by hiding the row and column headers and setting a scroll area...
Regards,
John :)
Bob Phillips
07-23-2006, 09:00 AM
Why not just a csutom type with a formula of
=SUMPRODUCT(--($C$9:$C$14=C14),--($D$9:$D$14=D14))>1
in DV?
johnske
07-23-2006, 03:23 PM
I can't get that to work Bob...
The 1st FirstName and Surname is in B4 and C4, so what should the DV formula be in (say) B8 and C8? :)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.