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" wrote in message
...
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" wrote in message
...
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
"Bob Phillips" 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" wrote in message
...
On Tue, 19 Jul 2005 22:51:50 +0100
In article
"Bob Phillips" 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
"Bob Phillips" 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
Debra Dalgleish 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
Debra Dalgleish 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" wrote in message
...
On Tue, 19 Jul 2005 18:55:58 -0400
In article
Debra Dalgleish 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