Data Validation

Topic

New Reply

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

Reply | Quote

Replies

Does this answer your question:
http://windowssecrets.com/forums/showthread//107381-only-allow-unique-value-in-cell-(xp-2003)?p=616897&viewfull=1#post616897

Steve

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

You might try looking at this article also. http://www.contextures.com/xlDataVal03.html

Reply | Quote

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

Reply | Quote

You can never stop people from being numpties… 😉

Reply | Quote