Eliminating choices on data validation

[Guest]

I am not sure how to do this, but I hope it can be easily accomplished.

I would like to have Cell A1 to have a drop down box to choose data 1,2,3,4

Now, I would like the same for Cell A2 except eliminate the choice that was
chosen in cell A1.

So, if I choose 3 for cell A1 then my choices would only be 1,2,4 for cell
A2. Is this possible and how do I do it?

TIA, Tim

 

[Guest]

Hi
Add the following code in the Worksheet_Change event

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sList$, i%
If Target.Cells(1, 1).Address = "$A$1" Then
For i = 1 To 4
If i <> Sheet1.Cells(1, 1).Value Then
If sList <> "" Then sList = sList & ","
sList = sList & i
End If
Next i
With Sheet1.Range("A2").Validation
.Delete
.Add xlValidateList, , , Formula1:=sList
End With
End If
End Sub