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.

Data Validation Drop-Down List Problem
 

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.

Data Validation Drop-Down List Problem
 

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.

Data Validation Drop-Down List Problem
 

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

Data Validation Drop-Down List Problem
 

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

Data Validation Drop-Down List Problem
 

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

Data Validation Drop-Down List Problem
 

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

Data Validation Drop-Down List Problem
 

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

Data Validation Drop-Down List Problem
 

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