Nesting drop down lists? (Excel 2007)

[Msifit_01]

Can you make a drop down list appear within your original drop down list?
I have a list of items: box, ball, aplle, scissors, stalper. (for example)
and I want to be able to select from different lists depending on which
number I am choosing.
For Example, if I selected stapler I would have a choice of 1, 2 or 3
staplers. However if I chose apple I would be chosing between red, yellow, or
green. And so, on.

I would need to link and nest the lists somehow. Is this possible? How do I
go about doing this?

 

[T. Valko]

See this:

http://contextures.com/xlDataVal02.html

 

[Msifit_01]

Not exactly what I am loking for. Though nice to know I can do that also.
What I want is for a "nested" drop down box within the same cell as my
original drop down box. So, like when I select the file button in Explorer
and a list pops up and then I scroll over new and anothr list box appears.
Only if I could do this in Excel my final selection would show up in a single
cell. Any other ideas?

 

[T. Valko]

I don't know if that's possible. If it is, I sure don't know how to do it.

 

[Don Guillett]

Biff, Never say never. Agreed that it's a strange way to do it, but this
makes a new vl in the same cell and then reverts to the original when the
second selection is made.

right click sheet tab>view code>insert this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> Range("h3").Address Then Exit Sub
Select Case LCase(Target)
Case Is = "a": X = "d,e,f"
Case Is = "b": X = "g,h,i"
Case Is = "c": X = "k,l,m"
Case Else: X = "a,b,c":MsgBox Target 'or other code
End Select
Application.EnableEvents = False
With Target.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:= _
xlValidAlertStop, Operator:= _
xlBetween, Formula1:=X
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Target = ""
Application.EnableEvents = True
End Sub--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[T. Valko]

Ok, I see what you're doing but I thought the OP wanted something like a
cascading menu (or maybe not!). I guess that could still be done but that's
out of my league!

 

[Msifit_01]

Um.... Well, I guess I'm going to give this a shot- a little over my head but
you never know, maybe I'll be able to figure it out. If I get it to work I'll
let you know.
Thank you!

 

[Msifit_01]

Yes, I do want a "cascading" set of lists. Does what Don provided me with not
do that? I have everything typed in but have no idea what to do once it has
been typed in. I'd like to try this and see if it will work, but ideally I am
looking for a way have cascading lists that increase in specificity as I make
my choices. Maybe I'm trying for something that is too complicated for me.

 

[T. Valko]

I do want a "cascading" set of lists.

That's how I interpreted your question.

Does what Don provided me with not do that?

Correct, it does not produce a cascading list. It has a top level list of
a,b or c. Once you select one of those then a new list is generated with
selections related to a,b or c.

The only way to do what you want that I know of is to use dependent lists
which use separate cells as is described in the link I posted.

You might be able to use a set of user forms to get the cascading effect but
that's beyond my skill level.

 

[Msifit_01]

Ah, but I think I can still use what Don provided me even if it isn't nested
it will still give me the final result I am looking for. So, how do I use the
information he gave me with the lists that I already have?