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]

 

[Gerald Higgins]

Yeah I think this is do-able.

One way would be to use Data Validation for each column, using a LIST to provide the options, and make that list be populated by formulas dependent on the results of the previous choice(s).

This type of request is quite common on this board - if you do some searching, you should be able to find similar questions with answers posted.

If you're still struggling, post back and I'd be happy to give you an illustration for a simplified scenario, to get you started.

 

[Don Fardie]

Thanks Gerald for your reply. I figured it was doable using a Data validation as the means, but not sure how to set the formulas up to list the other criteria based on which material is listed. For an example I didn't want a huge list of Grade of material being shown, if they don't relate to the material chosen in column A. Tried searching, but no luck. If you can offer any help or point out a posting that would show this scenario it would be appreciated big time....Thanks

 

[Gerald Higgins]

OK let's consider a simplified situation where you have only two materials, and each material has only two grades - ALUMINUM (6061 and 6063) and STEEL (Cold and 4140).

Let's say you choose your material in cell A2, and you choose your grade in cell B2.
The Data Validation for A2 can be a simple list maintained in the Data Validation dialog box, but it might be preferable to do this in a defined range on the sheet somewhere.
That's up to you.

Maintain a master list of materials and grades, like this, let's say in the range D1:E5

Material.....Grade
Aluminum...6061
Aluminum...6063
Steel........Cold
Steel........4140

Then, set up another list, which pulls in the selected material, and looks up the available grades based on that choice.
Let's say you do this in the range G1:H3, like this
Material.....Grade
Formula1....Formula2
Formula1....Formula2

Formula1 is simply
=A2
to repeat the material selected in cell A2.

Formula 2 is
=INDEX(E$2:E$5,MATCH(G$2,D$2:D$5,0)+ROW()-2,0)

The function of the ROW element is to calculate the offset of the first, second, third (and so on) material from the top of the list.
This will evaluate to zero on row 2, 1 on row 3, and so on.
You will need to modify this slightly, if your index formula does NOT start on row 2.

Then, your Data Validation for cell B2 (grade) should depend on the list in range H2:H3

See if you can get this to work for GRADE, and if so, you should be able to then move on and do the same for the other criteria.

I haven't done this myself for multiple criteria like this, you might need interim formulas to list all the possible permutations, IF values for RAW SHAPE and FINISH can be shared across multiple material / grade combinations.

 

[Don Fardie]

Thanks Gerald....I'm putting out some fires here and I'm out tomorrow. I will try my best to try this before the end of today, but it mat be a day before I can do it. It always happen when you schedule a day off and the you know what has to hit the fan the day before!!! Thanks again and I promise to jump on this as soon as I can!

 

[Gerald Higgins]

No rush at my end . . .

 

[Don Fardie]

Thanks Gerald...that worked great. Took a few times, but figured it out (cell assignments!!). The only issue is when you choose a new Material in A2 all the info previously selected stays posted until you click on the cells again (b2, b3, b4) to see the new listings of selections to choose from. Not sure if that can be can even be corrected back to a blank or "0" cell once A2 is changed to another Material, but it would help by not showing Grades and Finishes etc. that are not pertinent to the new material just selected. I'll keep plugging away and thanks for your help....really appreciate it.
Happy Turkey Day!

 

[Gerald Higgins]

Do you have recalculation set to manual ? Try switching it to automatic.

 

[Don Fardie]

Not sure if I explained that correctly. I do have the sheet set as Automatic and the range of cells that have the formulas (G1:H3 ) are recalculating correctly when the material is selected. What I was referring to if you change the Material selection below (A2) say to Aluminum the Grade selection (B2) will still show the Steel Grade that was previously selected, which in this case is 6061. You have to click on the cell to see the correct choices for Aluminum. Again not sure if there is a option to clear cell B2 automatically as soon there is no matched information between B2 and the new material in A2..........Hope I'm not a pain in the you know what!!

[TABLE="width: 194"]
<colgroup><col width="129" style="width: 97pt; mso-width-source: userset; mso-width-alt: 4717;" span="2"> <tbody>[TR]
[TD="class: xl67, width: 129, bgcolor: #D0CECE"]Material[/TD]
[TD="class: xl67, width: 129, bgcolor: #D0CECE"]Grade[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Steel[/TD]
[TD="class: xl66, bgcolor: transparent"]6061[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 258"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col span="2"></colgroup>[/TABLE]

 

[Gerald Higgins]

Oh I see. There are some ways around this.
One way might be to simply use a formula to show a warning message when you're in this kind of situation.
Another way might be to use VBA to clear out the Grade selection, whenever a new Material is specified.