Data Validation List and Custom formula in 1 cell

[mgirvin]

Dear Smartest Excelers In The World,

Is there a way to have Data Validation List and Data Validation Custom formula in the same formula?

Here is the complete problem description:

1) I made a list of 20 first-names of people on Sheet3, one first-name per cell, no formulas or anything on Sheet3, it's all typed letter by letter.

2) I named those cells on Sheet3 NAMES so that I can use Data Validation on it afterwards

3) I made 15 data validation cells on sheet1, each one is set to allow: LIST and the source for each one is NAMES

4) I'm happy because in each of those data validation cells on Sheet1 I have a dropdown list allowing me to choose amongst any of the firstnames on sheet3

5) I'm unhappy because I can choose one firstname in one data validation cell in sheet1 AND THE SAME ONE in another data validation cell in sheet1
(And this is for a timetable so I don't want 2 firstnames to be able to have the same timetable)

6) I found a method for disallowing duplicates amongst all my data validation cells: (hypothesis: let's say my 15 Data Validation cells on sheet1 are in A1:A15)
I select A1 then I select A1:A15, I go to DATA VALIDATION, I allow CUSTOM, and in SOURCE I put: =COUNTIF($A$1:$A$15,A1)=1

7) Now I cannot put the SAME firstname, in 2 different cells amongst my15 data validating cells in sheet1 BUT I lost my drop down list!!!

8) I want both at the SAME TIME in each of my 15 data validation cells on sheet1!!! (A dropdown data validation list AND disallow duplicates amongst those 15 data validation cells on sheet1)

Any ideas?

 

[darkanger4]

I don't understand what is happening in your circumstance. You say Mike's code works "to some extent", what does that mean?

The part of Mike's code that removes the duplicate value is highlighted red above. That occurs immediately before the message box, so I don't see how the message box could appear without the cell being cleared.

Perhaps you could give more detail about what you have and what you are trying to achieve as there must be something different about your setup.

Thanks for your reply Peter_SSs!
When I pick the value from the list it doesnt remove it, but if I type it manually then it is being removed before the message box, problem is that the value is rather large - 10 digits invoice number, so I prefer to pick it from the list. Can something be done so the code removes the duplicate value when it is picked from the list?

 

[Peter_SSs]

When I pick the value from the list it doesnt remove it,....
Can something be done so the code removes the duplicate value when it is picked from the list?

The code already removes the duplicate for me, even when choosing the values from a drop-down list of 10-digit numbers. So something must be different between what you have and what I have. Are there any more details you can give?

 

[darkanger4]

It turns out to be the version of Excel I am using - Excel 2002. Your reply made me think and I tried the same code on Excel 2010 and it works perfectly. Thanks for your time and help, much appreciated.
Have a great day!

 

[Peter_SSs]

OK, thanks for letting us know.

 

[Gabriel222]

Thank you !

 

[Rubbertoe]

Thank you so so so so much for this. It feels awesome knowing that there are people willing to help. I will pass it on.