Data Validation - entry must NOT equal any of the values in a specified range

[NewOrderFac33]

Good morning,

Please can someone suggest the formula for data validation whereby no value from the range A2:A50 is allowed in the cells where the validation rule is applied?

Cheers

Pete

 

[Just Jan]

G'day Pete

Custom formula in Validation
=COUNTIF($A$2:$A$50,A51)=0 apply to A51 onwards

HTH

 

[NewOrderFac33]

Hi, Jan,

I tried this, but it seems to still allow me to type in any value.

To be more specific, I'm trying to prevent the entry of duplicate values in the named range "Level_All".

So, if I have already entered a value in a cell within "Level_All", trying to enter it again into another cell in "Level_All" will throw up an error message.

Just wondering what would happen if I pressed F2 and Enter in a cell within that range...

I have worked out the VBA event code to achieve this, but I'm trying to move away from VBA solutions that only I, within my team, can support.

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo errorexit
    'If Not Intersect(Target, Range("Level_All").EntireRow) Is Nothing Then  'Insertion point within "Level_All's rows
    If Not Intersect(Target, Range("Level_All")) Is Nothing Then            'Target is WITHIN "Level_All" itself
        'MsgBox ("Within Data range")
        For Each Mycell In Range("Level_All")
            If Target.Cells.Count = 1 Then                                  'Just for changes to a single cell (avoids errors when clearing a range)
                If Len(Target.Formula) > 0 Then                             'Ignore blank cells
                    If Target.Address <> Mycell.Address Then                'Ignore current cell
                        If UCase(Target.Value) = UCase(Mycell.Value) Then   'If cell's value matches that of any other cell in "Level_All"
                            MsgBox ("You have already entered this value in cell: " & Mycell.Address & "!")   'Error message
                            Application.EnableEvents = False                'Turn off event handling before deleting cell value
                            Target.ClearContents                            'Delete cell value
                            Application.EnableEvents = True                 'Turn event handling back on after deleting cell value
                        End If
                    End If
                End If
            End If
        Next
    End If
    Exit Sub
errorexit:
End Sub

Cheers

Pete

 

[aequitas1903]

G'day Pete

Custom formula in Validation
=COUNTIF($A$2:$A$50,A51)=0 apply to A51 onwards

HTH

Just a little correction to Just Jan's answer:

=COUNTIF($A$2:$A$51,A2)=1 apply to A51 onwards

Please let us know if it works

 

[NewOrderFac33]

Cheers, Aequitas, but still no luck.

To allow for multiple occurrences of the duplicated value, I even tried:

=COUNTIF(Level_All,B12)>0

But I can still enter values that already exist in Level_All

The really daft thing is, I understand the formula exactly, it LOOKS correct, but it still doesn't work.

This is going to be a Facepalm moment when I finally get it..!

Pete

 

[NewOrderFac33]

if(COUNTIF(Level_All,B7))>0

works, although it actually prevents you from entering ANY value, since, as soon as you do, it becomes part of Level_All, and thus is not allowed.

I don't actually think that this is possible - I'd have to be entering a value in somewhere OTHER than Level_All for it to work, (note that my VBA solution excludes the currently selected cell, which the Excel formula doesn't seem to be able to do) but thank you both for giving me the solution for if I want to do something similar in the future!

Pete

 

[vds1]

Hi Pete,

whats the range for Level_All?

 

[aequitas1903]

As far as I understand,

You want a double validation where data should be selected from a list which you named "Level_All" and it should be different than the values which entered before(selection is available for once). That's a tough one I tried something like that before but did't manage to find a solution.

I have some stuff in my mind to try (helper column etc.). Let me try and I will let you know if I can come up with something.

Cheers

 

[aequitas1903]

I think I have a solution but dunno if you gonna like it

Think of your Level_All list is referred to C1:C5
The range you want to validate is A1:A5
The helper column/range is D1:D5

D1 formula: =IF(ISERROR(VLOOKUP(C1,$A$1:$A$5,1,0))=FALSE,"N/A",C1) and copy onwards to D5
this one is checking if a value is entered in the area you want the validation for (in this case it's A1:A5) and if used return "N/A", if not used returns your value.

And if you use custom validation on A1, formula for validation: =ISERROR(VLOOKUP(A1,$D$1:$D$5,1,0))=TRUE and copy validation onwards to A5

Con of this solution is that the user can not see the options he/she has to enter(No dropdown). But if you write an explanation to Error Alert like " Please select and item from "Level_All" and please be sure the value is not entered before between A1:A5 range." or something you would like to write.

Anyways please let me know if that fits for you otherwise I will try smthelse.

Cheers

 

[Just Jan]

Are there any duplicates in the range A2:A50? I suspect not as what you seek to achieve is the banishment of duplicate entries, so what rule applies to A51 is equally valid for cell A2. ie you cannot enter in A3 the same value in A2.
In which case the following will not allow the entry of any value already existing in the list

=COUNTIF($A$2:$A50,$A2)=1 apply to cell A2 then down

If duplications within the range A2;A50 allowed then someone with more experience may come up with a better solution as it seems you have a mindbender of a question here.