Selection from one drop down creates new drop down

[davethacker]

In the attached example I am trying to get the selections from one drop down to gnerate available selections in the next. The first drop down is the door style. This then needs to generate the possible drop downs in wood species. This then needs to generate the possible drop down in available stains. this then needs to generate the related door factor. apreciate any help that you can give.

Thanks,
Dave

Excel_validation_problem.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2	SelectDoorStyle
3	SelectWoodSpecie
4	SelectAvailablestain
5	Doorfactor
6					Availablewoodspecies					Doorstyles	Availablewoodspecies
7					Oak	Alder	Maple	Cherry			Oak	Alder	Maple	Cherry
8				AvailableStains	Natural	Natural	Natural	Natural		Charleston	1.48	1.48	1.53	1.65	Doorfactor
9					Autumn	Golden	Golden	Golden		Concord	1.3	1.3	1.36	1.42
10					Nutmeg	Autumn	Autumn	Autumn		Boston	1.42	1.42	1.48	1.59
11					Java	Sienna		Sienna		Savannah	1.36	1.36	1.42	1.48
12					Pacifica	Brandy		Brandy		Dakota	1.48	1.48	1.53	1.59
13					Juniper	Cinnamon		Cinnamon		Wyoming	1.36	1.36	1.42	1.48
14					Graphite	Nutmeg		Nutmeg		Colorado	1.53	1.53	1.59	1.65
15								Java		Montana	1.42	1.42	1.48	1.53
16
Sheet1

[/code]

 

[qmwhitlock]

Wow, Von Pookies solution was much simpler than mine. I have so much to learn about Excel.

Quinten

 

[davethacker]

Quinten,
Wow, That is a lot of work you did. Thank you very much! I think that your VBA formula probably works. Frankly it is a little daunting... I sure wish I understood all the code. You type with more than two fingers... right? I may still need your solution because....

Kristy,
Your solution and formula worked perfect. For about ten minutes my life was easy and my troubles were few. I could finally rest my weary mind but then.... I noticed another problem and my heart sank. #&*%

Because you guys are way smarter than me you may know how to fix this.
In my example the drop down lists are generated by the previous selection. The problem is that when I select a wood specie that allows Java stain and I select Java stain... It remains in the box. Now if I jump back and select Maple as a wood specie Java remains. This means that people using the form I am creating can now print an order sheet with a stain selection in a wood specie that is not available. Is there a way to correct this?

Thanks for your help. You guys have been awsome!

Dave

 

[Von Pookie]

Ah. I didn't think of that. I think you would need to use a macro for that. It wouldn't be anything very complicated, though. Just something small to clear the value of the other dropdowns whenever one is changed.

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

'if B3 was not the cell that was changed, exit macro
If Intersect(Target, [B3]) Is Nothing Then Exit Sub

'otherwise (if B3 *was* the changed cell)

'clear contents of B4
Application.EnableEvents = False
[B4].ClearContents
Application.EnableEvents = True

End Sub

Right-click the tab of the sheet and select 'View Code.' The Visual Basic Editor should open and display the module for that sheet. Paste the code in the module and it should automatically run any time you make a change on the sheet--but only clear B4 if the change is detected in B3.

 

[davethacker]

Kristy,
It works!!! You are one smart person.
This is awesome. Thank You!! Maybe I will rename my cat Kristy in honor of you... I don't know if she will go for the Von Pookie thing but....one never knows...I can hear myself now... heeere pookie pookie pookie. Kinda has a ring to it. Problem is I'm in the bass range. The neighbors will die laughing. Sorry... I'm a little giddy with delight.

Thanks again for your help

Dave

 

[davethacker]

adding to code to clear the next box

Kristy,
The code that you gave me works great. Now I need to have the box above clear out selections as well when door styles change. In other words I need the exact same thing to happen when the door selection is changed. The reason is that some wood species are not available in all of the doors. I have been able to get all selections to work as I want but if I go back and change the door style then a wood specie is left that is not available. I want to have it clear so that a person is forced to re select wood specie whenever the door style is changed. Does this make sense.

Thanks for any more help you can give.

Dave

 

[Von Pookie]

Sure, you can just tweak the macro to check for that as well.

This would go in place of the one I wrote previously:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
'if B3 (wood species) was the changed cell
If Target.Address = "$B$3" Then
    'clear contents of B4
    [B4].ClearContents
'else
'if B2 (door style) was the changed cell
ElseIf Target.Address = "$B$2" Then
    'clear contents of both B3 (wood species) and B4 (avail. stain)
    [B3:B4].ClearContents
End If
Application.EnableEvents = True

End Sub

 

[davethacker]

Kristy,
Once again your code works perfect.
Thanks for your great help.
I will rename my other cat Kristy in your honor. Problem is... he may not like it. Too bad huh! If he won't answer to Kristy then he won't get a Tuna Treat!

Say.. I am going to run out of honorary cats...

Thanks again,
Dave