Data validation question

[Peter]

Hi,

Can anyone please tell me if it is possible to have a cell, say B1, to
have two data validation lists dependant on a the contents of another
cell?

What I mean is:

If Cell A1 = 1 then cell B1 will use the data validation list C1:C10,
but if A1 = 2 then cell B1 will use data validation list D110

Any suggestions of howto obtain this result (if it is possible) would
be much appreciated.


--
Cheers

Peter

Remove the INVALID to reply

 

[merjet]

Put the following in the Worksheet's code module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$B$1" Then
If Range("A1") = 1 Then
With Range("B1").Validation
.Delete
.Add xlValidateList, xlValidAlertStop, xlBetween,
"=$C$1:$C$10"
.InCellDropdown = True
End With
End If
If Range("A1") = 2 Then
With Range("B1").Validation
.Delete
.Add xlValidateList, xlValidAlertStop, xlBetween,
"=$D$1:$D$10"
.InCellDropdown = True
End With
End If
End If
End Sub

HTH,
Merjet

 

[JON JON]

Hello Peter,

This suggestion use no VBA code but you will also lose the functionality of
Drop-down.

In your data validation use custom and put this formula. Take note that if
A1 is neither 1 or 2 B1 will accept any entry. Also if there is already
value in B1 and you change value in A1 value in B1 will not be re-validated.
Only when you change again B1.

=IF(A1=1,OR(B1=(C1:C10)),IF(A1=2,OR(B1=(D110)),""))

Regards,

Jon-jon

 

[Peter]

Put the following in the Worksheet's code module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$B$1" Then
If Range("A1") = 1 Then
With Range("B1").Validation
.Delete
.Add xlValidateList, xlValidAlertStop, xlBetween,
"=$C$1:$C$10"
.InCellDropdown = True
End With
End If
If Range("A1") = 2 Then
With Range("B1").Validation
.Delete
.Add xlValidateList, xlValidAlertStop, xlBetween,
"=$D$1:$D$10"
.InCellDropdown = True
End With
End If
End If
End Sub

HTH,
Merjet

Hi Merjet,

Thanks for your reply - I think this is exactly what I need.
I have entered the above in what i think is the code module of a
spreadsheet, with two lists in columns C & D, but it doesn't work - I
guess I must be doing something wrong - I'm afraid it isn't obvious.
Any suggestions as to what I need to do would be much appreciated.


--
Cheers

Peter

Remove the INVALID to reply

 

[merjet]

Thanks for your reply - I think this is exactly what I need.

I have entered the above in what i think is the code module of a
spreadsheet, with two lists in columns C & D, but it doesn't work - I
guess I must be doing something wrong - I'm afraid it isn't obvious.
Any suggestions as to what I need to do would be much appreciated.

If you double-click on the sheet's icon in the VB Editor, do you see the
code? Does cell A1 have a 1 or 2? Put a breakpoint in the code and
select B1 to check if the code is being executed.

Merjet