Data Validation Warning Message

Check out Ron's solution here.
You probably can't have two data validations in the same range.His solution is to remove the item from the list once it has been selected.

http://www.excelforum.com/excel-gene...tion-list.html

I'm attaching the workbook again, I didn't notice I deleted the Data Validation List.

The code is using countif , (not count or counta) and does appear to work for both text and numeric values.

It something to do with when the validation is triggered because if you click on the validation cell and hit enter it does display the message. One thing I noticed though, shouldnt your code be >1 rather than =1 as the count will always be at least 1 as the item youve just selected counts.

*Thats not the reason for the problem though, if anything that would have triggered the message on every selection.

Here is a VBA approach, select the drop down and when you have a duplicate in the range, a msgbox will pop up.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range, x
    Set rng = Range("B8:B14")
    If Target.Count > 1 Then Exit Sub    ' this stops code error if more than one cell is changed at once

    If Not Application.Intersect(Target, Me.Range("B8:B14")) Is Nothing Then    ' indicates the Target range

        x = WorksheetFunction.CountIf(rng, Target)
        If x > 1 Then
            MsgBox "Duplicate Entry", , "What!!"
            Target.Select
            Target.ClearContents
        End If
    End If

End Sub

While you could do that...I skip the need of an alert system by removing the option from the list after it is selected. Take a peek at this workbook on sheet1. In col A there is a formula counter that finds out if the item has been used before.

D is a static count (Should be up to max options, I went well past your total count)
E is going to pull over all of the unused items from the list in B (Which should be your full list) based on what A finds has not been used.
The validation (Named Range) is E2:E22 (Or the Max set of options)

Now when one is selected in the blue box the next time someone tries to select an option the previous will not be available, eliminating a need for an error.

Data Validation Example_DynamicList.xlsx

Originally Posted by ELeGault

While you could do that...I skip the need of an alert system by removing the option from the list after it is selected. Take a peek at this workbook on sheet1. In col A there is a formula counter that finds out if the item has been used before.

Although I think thats a perfectly fine way to do it there will be scenarios where removing the item from the list is not an acceptable way. For instance, if this was a list of payments made then you'd need to allow the user the facility to record the fact this 2nd payment had been made and then highlight the error. Removing it from the list may cause users to just ignore the item and move to the next one thereby never highlighting the problem.

if this was a list of payments made then you'd need to allow the user the facility to record the fact this 2nd payment had been made and then highlight the error.

I see where you are going with this, but in the scenario presented by the OP the alert/warning would restrict the user from being allowed to use this. If it was just to illustrate that this is a duplicate then we could apply some CF to bring that to notice or use the data validation to alert that this is a duplicate value as an FYI. Otherwise you can add further logic to the count statement that would say when and if something occurs to keep it in the count. Really that count list can become quite complex and is in many of the actual applications I use it for - just an option and your point is well noted. I do think for the scenario (IF I read correctly) then this should suffice in the OP's needs. But it is late so lol

Cheers

I agree totally, the solution youve provided is ideal for this post, I was highlighting the fact that anyone pulling this thread up in future as a possible solution should check their requirements before implementing any of the solutions raised (that goes to be honest for every thread in here, solutions found here (or anywhere else on the net) are not always guaranteed to solve someone elses problem).

It nice and early here, already 2.5 hours into the working day!

Agreed - Yea Im about to sleep for 5 hours then off to work lol I am a bit of an insomniac though so... Don't really feel tired, just know I need to sleep

This use of Data Validation lists will not allow duplicate entries because as an item is chosen, it is removed from the available items. The Data Validation lists are set up on Sheet1.

Didn't like my post version of that eh NDM

@EleGault

No problem at all with your post In fact I may not have seen it because for some reason at certain times of the day, posts seem to be held somewhere for times ranging from a few minutes to several hours I think it has to do with the Internet Gateway into and out of Canada. I have had this problem with other sites.

We had basically the same idea. I simply used a slightly different method and added a dynamic quality to the Data Validation drop down list....as items are used, the list actually gets shorter so there is no white space in the listing. Davesexcel pointed to another solution that was quite similar but wouldn't handle text strings for data mainly due to the use of the COUNT function instead of COUNTA.

Not being sure of the ability of the OP, a variety of possibilities doing pretty much the same thing will show that there is variety in what will work.

NDM - No worries, I didn't dive into yours too much just noticed it a little later and made me smile - Actually I rather like that we are on the same page to eliminate the error process and prevent it all together.

Have not looked but I am guessing that Dave adjusted it accordingly - Cheers

I didn't have to change anything

To me, eliminate errors where possible, then you don't have to deal further with them.

Dave's solution uses a macro to flash an error if a duplicate entry is attempted. The duplicate value is still in the DV list. All this just shows that there are many ways to "slay the cat" in Excel. The only thing that matters is that the solution suits the person who is going to use it.

@davesexcel

I see that your bird has its Christmas toque on

It was just a bird, but a few years ago teylyn added a toque to it. Haven't had the heart to remove it since.

Sorry for the delay. I received notification of your message 21 hours after you posted it.

The problem that you encountered was due to the definition of the TEAMS name in the Name Manager.

This should work.

Ok, here is how the formula should have been written so that when a choice is made from either column I or column Q the item will not be available again in either listing.

Formula:

=IF((COUNTIF($I$5:$I$28,K5)+COUNTIF($Q$5:$Q$28,K5))=0,MAX(L$4:L4)+1,"")

The enclosed is the result:

Thank you for the feedback and the rep.