Need help dependent list box and maybe a lookup formula

[clhmms]

Hello gracious gurus,

I need assistance with dependent list boxes. I have reviewed about 7+ videos and cannot find my answer.

I need a formula that will show a List Box based on formula. For example, if I select model "CT4-3/8" then, I want to have the "LB_TB_Fifteen_In " List Box choices available from cell B6. Or, if I select model "CT5-3/8 then, I will get the "LB_TB_TwentyTwo_In" List Box when I am in cell B6.

I have tried several approaches but I think the problem is that I am not able to logically wrap my brain around the formula. I have tried a dependent list box alone and that didn't work. I am assuming i need to toss in a lookup formula but I am confused!!

Any assistance would be greatly appreciated.
Cassandra

PS - Most recently, I tried Excel Data Validation -- Create Dependent Lists With INDEX
"http://www.contextures.com/xlDataVal15.html" and Excel Magic Trick 550: Data Validation List Drop-Down Based On 2nd Data Validation List Drop-Down Excel Magic Trick 550: Data Validation List Drop-Down Based On 2nd Data Validation List Drop-Down - YouTube
but could not get them to work for my scenario



````````````````````````````````````````````

Select Model	CT4-3/8	*Note- This is a list box which gets its data from another sheet
Select Hardware	? Avail Hardware LB based on Model	*Note- I need a formula that will display a List Box for the Avail Hardware

<tbody>
</tbody>

<tbody>
</tbody>

	Column D	Column AG
	Model	Avail Hardware
Row 22	CT4-3/8	TB_Eighteen_In
Row 23	CT4.5-3/8	TB_TwentyTwo_In
Row 24	CT5-3/8	TB_TwentyFour_In
	Column AH	Column AJ	Column AK
	LB_TB_Fifteen_In	LB_TB_Eighteen_In	LB_TB_TwentyTwo_In
Row 4	Type 1	Type 1	Type 1
Row 5	Type 2	Type 2	Type 2 Special
Row 6	Type 3	Type 3	Type 3
Row 7	Type 4	Type 4 Special	Type 4
Row 8	Type 5	Type 5	Custom
Row 9	Type 6	Type 6
Row 10	Custom	Type 7
Row 11		Type 8
Row 12		Custom

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[dspel79082]

Hi Cl,
what you need to do here is to use the name manager and name the ranges. The names must match the values in column AG exactly. Range AH4:AH10 would be name TB_Fifteen_In, etc. After you have all the ranges named, you need a lookup formula to find the value that matches your selection in the first list box. I would use index/match to avoid having to sort the data, since it's not in ascending order required by vlookup.
For the data that your'e showing and assumning your drop down is in A2 the formula would be
=INDEX($ag$22:$ag$24,MATCH($a$2,$d$22:$d$24,0)). This is the bais for the formula you will want in the data validation for b6.
As the formula is written above it will bring back the name in the cell.
For example, if the drop down selects CT4-3/8, the above formula will return the value in cell AG22 which is TB_Eighteen_In . What we actually want isn't the value in the cell, but the range that the name in the cell refers to.

To get the range that it refers to you have to put the whole formula inside the indirect function.

This is what needs to go into your data validation for B6.
=indirect(INDEX($ag$22:$ag$24,MATCH($a$2,$d$22:$d$24,0)))

 

[clhmms]

THANK YOU!! It works! You have made my day. I sincerely appreciate the time you gave to me. Because you were very nice and took the time to help me (and explain your solution), today I will do something nice for someone in your honor.

Have a wonderful day!