Results 1 to 10 of 10

Thread: Data Validation

  1. #1
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Renfrewshire, Scotland
    Posts
    121
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Data Validation

    Hi,

    I planned to use Data validation to ensure that users can only select from a list of options, however I want to ensure that each entry selected from this list is unique - is data validation the best way forward?

    To give some more details I have a list of candidates to choose from and users are to select from this list of candidates in several cells - but I want to make sure that each candidate can only be selected once - any ideas?

    Thanks

    Alba

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Does this answer your question:
    http://windowssecrets.com/forums/sho...l=1#post616897

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Renfrewshire, Scotland
    Posts
    121
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Steve,

    Thanks for your reply - this goes part way to solve my issue. However I want to limit the available selection to a list of names as well as only allow the selection of each once. The only way that I know to provide a list of names is to use Data Validation, so is there a way that this can be combined with your solution?

    Thanks
    Alba

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I don't think you can do this with data validation. You either validate from a list or a formula. You could have the formula check uniqueness AND validate from a list, but there would not be a display of the names, the user would have to enter the name that matched an item in a list and not repeat it.

    It could be done with a macro and list box, with the macro creating the list at runtime and only putting in names that were not used. If you want to do it this way, could you attach a sample with an indication of where the validation will be cheking for uniqueness and either where the list will be stored or if you want the list maintained only in the code.

    Steve

  5. #5
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Renfrewshire, Scotland
    Posts
    121
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cool

    Hi Steve,

    Thanks again for coming back to me. I have attached a rough copy of what I am looking for - I only have 10 or so candidates to select from, that will not change - I had intended for their names to exist within the spreadsheet so that it could be used for creating the list for data validataion, but since it is a relatively small not-changing selection if it would be easier to have the names in code, then I have no objection.

    All this because I know that people will be unable to follow simple instructions and will forget that they cannot choose the same person twice..

    Thanks again!

    Alba
    Attached Files Attached Files

  6. #6
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Use an alternate source

    You can see in the attached file that I am using another list as a source for the data validation.
    This list uses
    =IF(ISERROR(MATCH(H5,$C$5:$C$12,0)),H5, "") to strip out any names that have already been used.
    Attached Files Attached Files
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Why not something like the attached?

    The user can enter numbers 1-8 next to the names. Data validation on the numbers ensures uniqueness and in range. The candidate list is a lookup based on the numbers that they enter.

    Steve
    Attached Files Attached Files

  8. #8
    Star Lounger
    Join Date
    Nov 2008
    Location
    Northeast US
    Posts
    81
    Thanks
    10
    Thanked 1 Time in 1 Post
    You might try looking at this article also. http://www.contextures.com/xlDataVal03.html

  9. #9
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Renfrewshire, Scotland
    Posts
    121
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Smile

    Wow - excellent replies. Thanks to each of you for your input. I have taken a little of each and managed to get a robust solution that will stop people from being numpties and filling things in incorrectly!

    Now all I have to manage is the explanation of how to open the file, pick their choices and email back!!

    Thanks once more, all the help has been invaluable.

    Alba

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You can never stop people from being numpties...
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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