Data validation question
 

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 D1:D10

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


--
Cheers

Peter

Remove the INVALID to reply

Data validation question
 

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

Data validation question
 

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=(D1:D10))," "))

Regards,

Jon-jon

"Peter" wrote in message
...
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 D1:D10

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


--
Cheers

Peter

Remove the INVALID to reply

Data validation question
 

On Thu, 09 Oct 2003 22:06:35 GMT, "merjet" wrote:

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

Data validation question
 

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