Not sure if this is a V-Lookup, Index, or what!

[Don Fardie]

Trying to provide a Quote sheet that lists the Materials,Grade of Materials, Raw Shape and Finish for a project. The Material selection can easily bedone through a Drop Down data validation list, but based on that selection I want the nextcell to only offer the Grades of Material associated to the Material selected.This goes for the remaining cells as well.... Raw Shape and Finish. Because there so many variablesdepending on what Material is first chosen, I want minimize the selectionsso they can only choose correct options. The example below is just a basic layout of the information to show the possible variables.
Thanks in advance!!!
[TABLE="class: MsoNormalTable, width: 441"]
<tbody>[TR]
[TD="width: 109"]

Material<o></o>​

[/TD]
[TD="width: 81"]

Grade<o></o>​

[/TD]
[TD="width: 183"]

Raw Shape<o></o>​

[/TD]
[TD="width: 215"]

Finish<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 109, bgcolor: transparent"]

Aluminum<o></o>​

[/TD]
[TD="width: 81, bgcolor: transparent"]

6061<o></o>​

[/TD]
[TD="width: 183, bgcolor: transparent"]BAR, Flat, Hex, Sheet<o></o>
[/TD]
[TD="width: 215, bgcolor: transparent"]Precision, Polished
[/TD]
[/TR]
[TR]
[TD="width: 109, bgcolor: transparent"]

Aluminum<o></o>​

[/TD]
[TD="width: 81, bgcolor: transparent"]

6063<o></o>​

[/TD]
[TD="width: 183, bgcolor: transparent"]BAR, Flat, Tube, Sheet<o></o>
[/TD]
[TD="width: 215, bgcolor: transparent"]Precision, Polished, Std
[/TD]
[/TR]
[TR]
[TD="width: 109, bgcolor: transparent"]

Aluminum<o></o>​

[/TD]
[TD="width: 81, bgcolor: transparent"]

7075<o></o>​

[/TD]
[TD="width: 183, bgcolor: transparent"]BAR, Flat, Tube, Hex, Sheet<o></o>
[/TD]
[TD="width: 215, bgcolor: transparent"]Precision, Std
[/TD]
[/TR]
[TR]
[TD="width: 109, bgcolor: transparent"]

Aluminum<o></o>​

[/TD]
[TD="width: 81, bgcolor: transparent"]

2024<o></o>​

[/TD]
[TD="width: 183, bgcolor: transparent"]BAR, Tube, Hex, Sheet<o></o>
[/TD]
[TD="width: 215, bgcolor: transparent"]Precision, Polished, Std.

[/TD]
[/TR]
[TR]
[TD="width: 109, bgcolor: transparent"]

Stainless<o></o>​

[/TD]
[TD="width: 81, bgcolor: transparent"]

6061<o></o>​

[/TD]
[TD="width: 183, bgcolor: transparent"]BAR, Flat, Tube, Hex,
[/TD]
[TD="width: 215, bgcolor: transparent"]Precision, Std.
[/TD]
[/TR]
[TR]
[TD="width: 109, bgcolor: transparent"]

Stainless<o></o>​

[/TD]
[TD="width: 81, bgcolor: transparent"]

6063<o></o>​

[/TD]
[TD="width: 183, bgcolor: transparent"]BAR, Flat, Tube, Hex, Sheet<o></o>
[/TD]
[TD="width: 215, bgcolor: transparent"]Precision, Polished, Std.
[/TD]
[/TR]
[TR]
[TD="width: 109, bgcolor: transparent"]

Stainless<o></o>​

[/TD]
[TD="width: 81, bgcolor: transparent"]

7075<o></o>​

[/TD]
[TD="width: 183, bgcolor: transparent"]Flat, Tube, Hex, Sheet<o></o>
[/TD]
[TD="width: 215, bgcolor: transparent"]Precision, Polished
[/TD]
[/TR]
[TR]
[TD="width: 109, bgcolor: transparent"]

Stainless<o></o>​

[/TD]
[TD="width: 81, bgcolor: transparent"]

2024<o></o>​

[/TD]
[TD="width: 183, bgcolor: transparent"]BAR, Flat, Tube, Sheet<o></o>
[/TD]
[TD="width: 215, bgcolor: transparent"]Precision, Polished, Std.
[/TD]
[/TR]
[TR]
[TD="width: 109, bgcolor: transparent"]

Steel<o></o>​

[/TD]
[TD="width: 81, bgcolor: transparent"]

Cold<o></o>​

[/TD]
[TD="width: 183, bgcolor: transparent"]BAR, Flat, Hex, Sheet<o></o>
[/TD]
[TD="width: 215, bgcolor: transparent"] Polished, Std.
[/TD]
[/TR]
[TR]
[TD="width: 109, bgcolor: transparent"]

Steel<o></o>​

[/TD]
[TD="width: 81, bgcolor: transparent"]

4140<o></o>​

[/TD]
[TD="width: 183, bgcolor: transparent"]Flat, Tube, Hex, Sheet<o></o>
[/TD]
[TD="width: 215, bgcolor: transparent"]Precision, Polished
[/TD]
[/TR]
[TR]
[TD="width: 109, bgcolor: transparent"]

Steel<o></o>​

[/TD]
[TD="width: 81, bgcolor: transparent"]

H Carbon<o></o>​

[/TD]
[TD="width: 183, bgcolor: transparent"]BAR, Flat, Tube, Hex
[/TD]
[TD="width: 215, bgcolor: transparent"] Polished, Std.
[/TD]
[/TR]
[TR]
[TD="width: 109, bgcolor: transparent"]

Steel<o></o>​

[/TD]
[TD="width: 81, bgcolor: transparent"]

L Carbon<o></o>​

[/TD]
[TD="width: 183, bgcolor: transparent"]BAR, Flat, Tube, Hex, Sheet<o></o>
[/TD]
[TD="width: 215, bgcolor: transparent"]Precision, Polished
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Don Fardie]

Sounds good....I'll give them a try. Thanks again and have a great Thanksgiving!

 

[Don Fardie]

Hi Gerald,
I did end up using use a VBA to clear the cells once the first cell is deleted, but after I protected the sheet the VBA runs an error. I've seen posts where they say add "Unprotect" and "Protect" into the code (if no password is used), but what happens is the sheet stays protected, but now it doesn't clear cells??? Not sure where exactly this is to be entered to work properly. Is this something you can help with? Thanks in advance....Don


Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("C16:C34")) Is Nothing Then
Range("I" & Target.Row & ":S" & Target.Row).ClearContents
End If
Application.EnableEvents = True
Application.EnableEvents = False
If Not Intersect(Target, Range("C38:C56")) Is Nothing Then
Range("D" & Target.Row & ":P" & Target.Row).ClearContents
End If
Application.EnableEvents = True
End Sub