Multiple DATA Validation for same cells

[kimjon76]

Hi,

I need support on how to do a multiple data validation entry in the same column (Same cells). I want to restrict the entries in B1 to B20 to only "X" and "B" in a drop down menue in each cell. (easy, just do a data validation and choose list. BUT, I also want to restrict the entries to maximum 10 X´s and 4 B´s in the cells B1 to B20. Is this possible at all?

Pls help.

br
Kim

 

[XOR LX]

Hi,

Not sure I understand. If each of the 20 cells in that range can only have either an "X" or a "B", how is it possible that you can ever have a maximum total of only 14 of those 20 having either an "X" or a "B"?

Regards

 

[kimjon76]

Hi XOR LX,

I want to limit the entries to only "X" and "B", but X should only be possible to be selected in 10 of the cells in range before it is blocked and B should only be possible to select in 4 of the cells in the range. Meaning that when x have been selected in 10 cells of the range, there should be an error message when you try to select x the 11th time. (Same for B, when you try to select B the 5th time, there should be an error message.

br
Kim

 

[MickG]

Try this:-
To insert code, In data sheet click "Alt+F11", vbwindow appears, Paste code into Vb window, close Vbwindow.
Enter "X" in "A1" , Validation Drop down for range completed.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
Dim Dn As Range
Dim x As Integer
Dim b As Integer
Dim str As String
Set Rng = Range("B1:B20")
If Not Intersect(Target, Rng) Is Nothing Then
    x = Application.CountIf(Rng, "X")
    b = Application.CountIf(Rng, "B")
        If x = 10 And b = 4 Then
            str = " "
        ElseIf x = 10 Then
            str = "B"
        ElseIf b = 4 Then
            str = "X"
        Else: str = "X,B"
        End If


With Rng.Validation
    .Delete
    .Add Type:=xlValidateList, Formula1:=str
End With
End If
End Sub

 

[kimjon76]

Hey MickG,

THANKS! I´m a excel rookie, how do I paste it into the VB window? I got the vb window to appear, but then what?

br
Kim

 

[kimjon76]

Hey, it WORKS! Thank you soo much. MickG. A follow up question; if I now want to do the same with the column C, can I do that in the same code? Meaning that the range in column C is behaving exactly as column B in the same sheet?

br
Kim

 

[XOR LX]

Create two lists, perhaps in a separate sheet, e.g.

In Sheet2 A1 and A2 enter "B" and "X" respectively (obviously without my quotation marks).
In Sheet2 B1 and B2 enter 4 and 10 respectively.

Your Custom formula for the Data Validation, entered whilst the range in Sheet1 B1:B20 is highlighted in the worksheet, is then:

=AND(OR(EXACT($B1,Sheet2!$A$1:$A$2)),MMULT(TRANSPOSE(ROW($B$1:$B$20)^0),(--(EXACT($B$1:$B$20,TRANSPOSE(Sheet2!$A$1:$A$2)))))<=TRANSPOSE(Sheet2!$B$1:$B$2))

Regards

 

[pgc01]

Hi Kim

This is a solution without vba

I'll use J2:J3 to build the list of allowed values.

In J2: =IF(COUNTIF($B$1:$B$20,"B")<4,"B",IF(COUNTIF($B$1:$B$20,"X")<10,"X","No value allowed"))
In J3: =IF(J2="X","",IF(COUNTIF($B$1:$B$20,"X")<10,"X",""))

Define the name:

Name: DataList01

Refers to: =OFFSET(Sheet1!$J$2,0,0,1+IF(Sheet1!$J$2<>"",Sheet1!$J$3="X"),1)

Select B1:B20 and in the Data Validation

List: =DataList01

 

[kimjon76]

Hi MickG,

This worked perfectly. THANKS! Now I want to do two additional changes to it. I also want to add maximum 4 "T"´s to the same range of cells. And I also want to make the same range of cells in columns C,D,E,F to behave in the exact way as the range of cells in column B. How do I do that?

Again, thanks for helping out.

br
Kim

Try this:-
To insert code, In data sheet click "Alt+F11", vbwindow appears, Paste code into Vb window, close Vbwindow.
Enter "X" in "A1" , Validation Drop down for range completed.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
Dim Dn As Range
Dim x As Integer
Dim b As Integer
Dim str As String
Set Rng = Range("B1:B20")
If Not Intersect(Target, Rng) Is Nothing Then
    x = Application.CountIf(Rng, "X")
    b = Application.CountIf(Rng, "B")
        If x = 10 And b = 4 Then
            str = " "
        ElseIf x = 10 Then
            str = "B"
        ElseIf b = 4 Then
            str = "X"
        Else: str = "X,B"
        End If


With Rng.Validation
    .Delete
    .Add Type:=xlValidateList, Formula1:=str
End With
End If
End Sub

 

[Joe4]

It looks like you managed to post this question three times. I merged the two with responses into this one, and got rid of the third.
If the board seems slow, don't hit the Submit button more than once when posting. Though it may take a few minutes, the question will post.

Thanks