by Beamer » Tue Feb 02, 2016 6:09 am
ConneXionLost's idea would indeed solve half your problem, but, if I'm reading your question correctly, you want to make sure that the same number can't be chosen twice.
If that is correct, then I suggest a helper column showing all numbers within your desired range that automatically removes any number that has been used. Then another column will re-list all those numbers using small to remove the blanks.
Then make the data validation use that list. (That will probably only work if it is a drop-down list).
Also, all previously used numbers would have to remain on the same sheet (to be searched for a match), if that is not the case then I think VBA will be needed.