Data Validation Drop-Down List Problem

  • Thread starter Thread starter AmberLeaf
  • Start date Start date
A

AmberLeaf

Just can't get my head around this prob at all.

In cell C2, a drop-down list gives 5 options to select from :
"1"-"2"-"3"-"4"-"5"

In cell D2 I'm trying to create drop-down lists for each selected
option in C2.

i.e. If "1" is selected from the drop-down list in cell C2, a drop
down list of half a dozen options would be available in cell D2.

If "2" is selected from the drop-down list in cell C2, a drop down
list of a different half a dozen options would be available in cell
D2.

The same would apply for all the other selections.

Does anyone know how I could get this function operating? Would be
very obliged for some help

Thanks in advance.
 
Use the worksheet change event
(this worked in Excel 2000)
Name your lists
Replace list names I used...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Lst As String ' Lst = list name for validation in cell D2
If Target.Address = "$C$2" Then
If Target = 1 Then
Lst = "=List1"
ElseIf Target = 2 Then
Lst = "=List2"
ElseIf Target = 3 Then
Lst = "=List3"
ElseIf Target = 4 Then
Lst = "=List4"
ElseIf Target = 5 Then
Lst = "=List5"
End If
End If

' Remove or change lines not needed..
With Range("D2").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=
_
xlBetween, Formula1:=Lst
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

End Sub
 
http://www.contextures.com/xlDataVal02.html
Click to expand...


Thanks for the prompt reply Bob.

I've been to this site before and tried the instructions to get my
problem sorted but I just can't get it to work - I find that if I try
to name a range with a number I get an error message from Excel saying
that it's looking for a valid name for the range.

I think it's because I'm trying to pick up different drop-down boxes
from a number instead of a text reference - if you see what I mean.

Cheers
 
You could name the ranges List1, List2, etc.
Then, in the data validation dialog box, for the dependent dropdowns,
use the formula:

=INDIRECT("List"&B3)
 
You could name the ranges List1, List2, etc.
Then, in the data validation dialog box, for the dependent dropdowns,
use the formula:

=INDIRECT("List"&B3)
Click to expand...
Thanking you Debra,

This did the job very nicely.

Thanks Steve & Bob for your help as well. I'm really going to have to
try to get my head around the VBA routines - they look quite
interesting to get to grips with.

Thanks again folks

AmberLeaf
 
Amber,

You're welcome...

Just stay with this ng and you'll learn a lot...
Lot's of good stuff here.

Also use the recorder. That will help you learn basic code.
 
Back
Top