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



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

[TABLE="width: 455"]
<tbody>[TR]
[TD]Select Model[/TD]
[TD]CT4-3/8[/TD]
[TD]*Note- This is a list box which gets its data from another sheet[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Select Hardware[/TD]
[TD]? Avail Hardware LB based on Model[/TD]
[TD]*Note- I need a formula that will display a List Box for the Avail Hardware[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 455"]
<tbody>[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 518"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD]Column D
[/TD]
[TD]Column AG[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Model
[/TD]
[TD]Avail Hardware[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Row 22
[/TD]
[TD] CT4-3/8
[/TD]
[TD]TB_Eighteen_In
[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Row 23[/TD]
[TD] CT4.5-3/8 [/TD]
[TD]TB_TwentyTwo_In[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Row 24[/TD]
[TD] CT5-3/8 [/TD]
[TD]TB_TwentyFour_In[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Column AH
[/TD]
[TD]Column AJ[/TD]
[TD]Column AK[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]LB_TB_Fifteen_In
[/TD]
[TD]LB_TB_Eighteen_In[/TD]
[TD]LB_TB_TwentyTwo_In[/TD]
[/TR]
[TR]
[TD]Row 4
[/TD]
[TD]Type 1
[/TD]
[TD]Type 1[/TD]
[TD]Type 1[/TD]
[/TR]
[TR]
[TD]Row 5[/TD]
[TD]Type 2[/TD]
[TD]Type 2[/TD]
[TD]Type 2 Special [/TD]
[/TR]
[TR]
[TD]Row 6[/TD]
[TD]Type 3[/TD]
[TD]Type 3[/TD]
[TD]Type 3[/TD]
[/TR]
[TR]
[TD]Row 7[/TD]
[TD]Type 4[/TD]
[TD]Type 4 Special[/TD]
[TD]Type 4[/TD]
[/TR]
[TR]
[TD]Row 8[/TD]
[TD]Type 5[/TD]
[TD]Type 5[/TD]
[TD]Custom[/TD]
[/TR]
[TR]
[TD]Row 9[/TD]
[TD]Type 6[/TD]
[TD]Type 6[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Row 10[/TD]
[TD]Custom[/TD]
[TD]Type 7[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Row 11[/TD]
[TD] [/TD]
[TD]Type 8[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Row 12[/TD]
[TD] [/TD]
[TD]Custom[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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!