Data Validation Drop-Down List Problem

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.

http://www.contextures.com/xlDataVal02.html

--
HTH

Bob Phillips

"AmberLeaf" <[email protected]> wrote in message
news:[email protected]...
> 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

--
steveB

Remove "AYN" from email to respond
"AmberLeaf" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
>

On Tue, 19 Jul 2005 22:51:50 +0100
In article <#[email protected]>
"Bob Phillips" <[email protected]> wrote:

>http://www.contextures.com/xlDataVal02.html


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 try this approach as an alternative
http://www.xldynamic.com/source/xld.Dropdowns.html#dv

--
HTH

Bob Phillips

"AmberLeaf" <[email protected]> wrote in message
news:[email protected]...
> On Tue, 19 Jul 2005 22:51:50 +0100
> In article <#[email protected]>
> "Bob Phillips" <[email protected]> wrote:
>
> >http://www.contextures.com/xlDataVal02.html
>
>
> 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)

AmberLeaf wrote:
> On Tue, 19 Jul 2005 22:51:50 +0100
> In article <#[email protected]>
> "Bob Phillips" <[email protected]> wrote:
>
>
>>http://www.contextures.com/xlDataVal02.html
>
>
>
> 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


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

On Tue, 19 Jul 2005 18:55:58 -0400
In article <[email protected]>
Debra Dalgleish <[email protected]> wrote:

>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)
>
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

You're welcome. Thanks for letting me know that it worked.

AmberLeaf wrote:
> On Tue, 19 Jul 2005 18:55:58 -0400
> In article <[email protected]>
> Debra Dalgleish <[email protected]> wrote:
>
>
>>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)
>>
>
> 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


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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.

--
steveB

Remove "AYN" from email to respond
"AmberLeaf" <[email protected]> wrote in message
news:[email protected]...
> On Tue, 19 Jul 2005 18:55:58 -0400
> In article <[email protected]>
> Debra Dalgleish <[email protected]> wrote:
>
>>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)
>>
> 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