+ Reply to Thread
Results 1 to 8 of 8

Data validation that removes names but there are multiple of the same name

  1. #1
    Registered User
    Join Date
    03-02-2016
    Location
    Dawson Creek, Canada
    MS-Off Ver
    365
    Posts
    22

    Data validation that removes names but there are multiple of the same name

    Hello everyone, I was wondering if there was a solution to this problem:

    I want to have a data validation list that removes names that are already used, the caveat being that it's an employees list and there can obviously be multiple people with the same first or last name. So if there are, for example, four "Bob"s, I'd like to have the name appear in the dropdown until it is selected four times, or two last names of "Williams" so it should be selectable twice, and then is no longer selectable if that makes sense. Thanks for any help.

    Sample.xlsx
    Last edited by Avsfan3319; 09-23-2023 at 01:01 PM.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,868

    Re: Data validation that removes names but there are multiple of the same name

    Please see the updated sample file. This is the way that I would do it.
    Attached Files Attached Files
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,868

    Re: Data validation that removes names but there are multiple of the same name

    Please change the filter to
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    otherwise you will get a #CALC! error when all the names have been allocated.

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,868

    Re: Data validation that removes names but there are multiple of the same name

    Updated sample file.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    03-02-2016
    Location
    Dawson Creek, Canada
    MS-Off Ver
    365
    Posts
    22

    Re: Data validation that removes names but there are multiple of the same name

    Thanks, but I need to keep the names in separate columns. However this did put me on the right track and after messing around for a bit,
    I believe I have it figured. I'll attach it in case anyone else finds it useful. I am by no means an expert and there may be better ways
    of accomplishing the end goal but this seems to work as I'd hoped. Thanks again @TMS for your time and help!!


    Sample DV TMS2.xlsx

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,868

    Re: Data validation that removes names but there are multiple of the same name

    You're welcome.

    It seems odd to me that you can mix and match first names and last names but, if that's what you want to do, go for it.

    The solution I provided still kept the first and last names separate, but combined them for the purpose of selection.


    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

  7. #7
    Registered User
    Join Date
    03-02-2016
    Location
    Dawson Creek, Canada
    MS-Off Ver
    365
    Posts
    22

    Re: Data validation that removes names but there are multiple of the same name

    Yeah I'd prefer to do it that way as well, with combining names myself but my employer wants it set up that way unfortunately...
    I did not know about the rep thing, and will definitely be sure to use it for yourself and everyone else that is so helpful. Thanks again.

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,868

    Re: Data validation that removes names but there are multiple of the same name

    You're welcome. Thanks for the rep.

    Strange that your boss wants the ability to roster Bob Sinatra and Frank Marley. Totally illogical. Even if wrong pairings are unintentional, the potential for human error, confusion and embarrassment is immense. Not sure why he would want that when it is avoidable. But guess he's paid the big bucks to make the stupid decisions.

    Good luck.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Using Data Validation to select one of five lists of names.
    By jaljr in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 06-17-2017, 02:20 PM
  2. [SOLVED] Unique names list from data validation column
    By L plates in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-01-2017, 07:08 PM
  3. Replies: 7
    Last Post: 12-23-2015, 07:43 AM
  4. [SOLVED] Data Validation Of Names Required from given List :confused
    By pipsmultan in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 07-26-2014, 08:12 AM
  5. Replies: 0
    Last Post: 10-22-2012, 06:52 PM
  6. Using Defined Names with Data Validation Depend and Data Validation Multi Select
    By Vinnie Chan in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 03-01-2012, 05:36 PM
  7. Data Validation Defined Names
    By ChemistB in forum Excel General
    Replies: 4
    Last Post: 02-22-2008, 02:18 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1