three level dependent combobox

[AnandKaushal]

Hi,

I want 3 combobox in my sheet which are depended upon previous combobox data and final cell display price depended upon combination

Data Structure:-

Breverage
TEA
COFFEE

TEA
GREEN TEA
BLACK TEA

COFFEE
BLACK COFFEE
REGULAR COFFEE

POTION
SMALL
MEDIUM
LARGE

PRICE
TEA -> GREEN TEA -> SMALL -> 10
TEA -> GREEN TEA -> MEDIUM -> 20 and so on depending on combination.

Please help me solve this.

 

[Jeffrey Mahoney]

Anand,

My experience with first time posters on this site is that they are looking for someone to do their project for them or they think their request should be very simple. When faced with a response that is more advanced and causes them to spend more than a few minutes to solve, they give up. I usually pass on first time posters because of that.

I have done this before successfully. The final product can be scaled automatically to include many more items in each list without having to change formulas or named ranges. It is advanced. It will require many steps. You will learn something.

If you accept this mission, instructions will follow that you will need to read in detail.

Jeff

 

[AnandKaushal]

Thanks sir

 

[Jeffrey Mahoney]

Am I to assume that you want to take the time?

Is that your real data set? Coffees and Teas, Size, and so forth? No big deal, seams generic. I will start working on it. How large will you lists be? Tens of numbers, hundreds; just a ballpark.

 

[Jeffrey Mahoney]

This is the Pulldown cells and the formula for getting the price. Cell D3 is the dependent dropdown cell that changes based on your selection in B3. B3 is the main choice, Tea or Coffee. Portion allows you to choose the size from a single list and isn't dependent. Price is the formula

More to come.

Excel 2013

	B	C	D	E	F	G	H
2	Beverage		Type Of Beverage		Portion		Price
3	Coffee		Decaf Coffee		Medium		$4.25

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Pulldown​

Worksheet Formulas Cell Formula H3 =IFERROR(VLOOKUP(BevSelected&","&D3&","&F3,FullPrice_list,2,FALSE),0) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Workbook Defined Names Name Refers To BevSelected =Pulldown!$B$3 FullPrice_list =OFFSET(Setup!$J$1,1,0,MATCH("zzzzzzzzzz",Setup!$J:$J)-ROW(Setup!$J$1),2) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Jeffrey Mahoney]

This is the Setup sheet. These are the maintained lists. All of the lists or tables have dynamic named ranges supporting the dropdowns.

Each of the names for the named ranges are at the top. As you add or remove items so does the list change in size. Columns B, D, E, H, and K are manually maintained by you. Column F is a series of formulas to aggregate the list based on the choice from the PullDown sheet. You only need to copy the formula into F2 and then copy that down. You may need to add more aggregate formulas as your list grows. Column J needs to be specifically formatted; the price formula is counting on no spaces after the commas that concatenate the beverages and portions. Column E defines which type of beverage it is from the Beverage_list.

Here are the named ranges. Copy the date below before creating the named ranges:
Agg_list =OFFSET(Setup!$F$1,1,0,MATCH("zzzzzzzzzz",Setup!$F:$F)-ROW(Setup!$F$1),1)
Beverage_list =OFFSET(Setup!$B$1,1,0,MATCH("zzzzzzzzzz",Setup!$B:$B)-ROW(Setup!$B$1),1)
BevSelected =Pulldown!$B$3
BevType_list =OFFSET(Setup!$D$1,1,0,MATCH("zzzzzzzzzz",Setup!$D:$D)-ROW(Setup!$D$1),1)
Full_List =OFFSET(Setup!$J$1,1,0,MATCH("zzzzzzzzzz",Setup!$J:$J)-ROW(Setup!$J$1),1)
FullPrice_list =OFFSET(Setup!$J$1,1,0,MATCH("zzzzzzzzzz",Setup!$J:$J)-ROW(Setup!$J$1),2)
Portion_list =OFFSET(Setup!$H$1,1,0,MATCH("zzzzzzzzzz",Setup!$H:$H)-ROW(Setup!$H$1),1)
Type_list =OFFSET(Setup!$D$1,1,1,MATCH("zzzzzzzzzz",Setup!$E:$E)-ROW(Setup!$D$1),1)

Excel 2013

	B	C	D	E	F	G	H	I	J	K
1	Beverage_list		BevType_list	Type_list	Agg_list		Portion		Full List	Price
2	Tea		Green Tea	Tea	Black Coffee		Small		Tea,Green Tea,Small	$2.50
3	Coffee		Black Tea	Tea	Regular Coffee		Medium		Tea,Green Tea,Medium	$3.00
4			Black Decaf Tea	Tea	Espresso		Large		Tea,Green Tea,Large	$3.50
5			Herbal Tea	Tea	Decaf Coffee				Tea,Black Tea,Small	$2.50
6			Black Coffee	Coffee	0				Tea,Black Tea,Medium	$3.00
7			Regular Coffee	Coffee	0				Tea,Black Tea,Large	$3.50
8			Espresso	Coffee	0				Tea,Black Decaf Tea,Small	$2.75
9			Decaf Coffee	Coffee	0				Tea,Black Decaf Tea,Medium	$3.25
10					0				Tea,Black Decaf Tea,Large	$3.75
11					0				Tea,Herbal Tea,Small	$2.50
12					0				Tea,Herbal Tea,Medium	$3.00
13					0				Tea,Herbal Tea,Large	$3.50
14					0				Coffee,Black Coffee,Small	$3.75
15					0				Coffee,Black Coffee,Medium	$4.25
16					0				Coffee,Black Coffee,Large	$4.75
17					0				Coffee,Regular Coffee,Small	$3.75
18					0				Coffee,Regular Coffee,Medium	$4.25
19					0				Coffee,Regular Coffee,Large	$4.75
20					0				Coffee,Espresso,Small	$4.00
21					0				Coffee,Espresso,Medium	$4.50
22					0				Coffee,Espresso,Large	$5.00
23					0				Coffee,Decaf Coffee,Small	$3.75
24					0				Coffee,Decaf Coffee,Medium	$4.25
25					0				Coffee,Decaf Coffee,Large	$4.75

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Setup​

Worksheet Formulas Cell Formula F2 =IFERROR(INDEX(BevType_list,AGGREGATE(15,6,ROW(BevType_list)-ROW(Setup!$D$1)/(Type_list=BevSelected),ROWS(Setup!$D$2:D2))),0) F3 =IFERROR(INDEX(BevType_list,AGGREGATE(15,6,ROW(BevType_list)-ROW(Setup!$D$1)/(Type_list=BevSelected),ROWS(Setup!$D$2:D3))),0) F4 =IFERROR(INDEX(BevType_list,AGGREGATE(15,6,ROW(BevType_list)-ROW(Setup!$D$1)/(Type_list=BevSelected),ROWS(Setup!$D$2:D4))),0) F5 =IFERROR(INDEX(BevType_list,AGGREGATE(15,6,ROW(BevType_list)-ROW(Setup!$D$1)/(Type_list=BevSelected),ROWS(Setup!$D$2:D5))),0) F6 =IFERROR(INDEX(BevType_list,AGGREGATE(15,6,ROW(BevType_list)-ROW(Setup!$D$1)/(Type_list=BevSelected),ROWS(Setup!$D$2:D6))),0) F7 =IFERROR(INDEX(BevType_list,AGGREGATE(15,6,ROW(BevType_list)-ROW(Setup!$D$1)/(Type_list=BevSelected),ROWS(Setup!$D$2:D7))),0) F8 =IFERROR(INDEX(BevType_list,AGGREGATE(15,6,ROW(BevType_list)-ROW(Setup!$D$1)/(Type_list=BevSelected),ROWS(Setup!$D$2:D8))),0) F9 =IFERROR(INDEX(BevType_list,AGGREGATE(15,6,ROW(BevType_list)-ROW(Setup!$D$1)/(Type_list=BevSelected),ROWS(Setup!$D$2:D9))),0) F10 =IFERROR(INDEX(BevType_list,AGGREGATE(15,6,ROW(BevType_list)-ROW(Setup!$D$1)/(Type_list=BevSelected),ROWS(Setup!$D$2:D10))),0) F11 =IFERROR(INDEX(BevType_list,AGGREGATE(15,6,ROW(BevType_list)-ROW(Setup!$D$1)/(Type_list=BevSelected),ROWS(Setup!$D$2:D11))),0) F12 =IFERROR(INDEX(BevType_list,AGGREGATE(15,6,ROW(BevType_list)-ROW(Setup!$D$1)/(Type_list=BevSelected),ROWS(Setup!$D$2:D12))),0) F13 =IFERROR(INDEX(BevType_list,AGGREGATE(15,6,ROW(BevType_list)-ROW(Setup!$D$1)/(Type_list=BevSelected),ROWS(Setup!$D$2:D13))),0) F14 =IFERROR(INDEX(BevType_list,AGGREGATE(15,6,ROW(BevType_list)-ROW(Setup!$D$1)/(Type_list=BevSelected),ROWS(Setup!$D$2:D14))),0) F15 =IFERROR(INDEX(BevType_list,AGGREGATE(15,6,ROW(BevType_list)-ROW(Setup!$D$1)/(Type_list=BevSelected),ROWS(Setup!$D$2:D15))),0) F16 =IFERROR(INDEX(BevType_list,AGGREGATE(15,6,ROW(BevType_list)-ROW(Setup!$D$1)/(Type_list=BevSelected),ROWS(Setup!$D$2:D16))),0) F17 =IFERROR(INDEX(BevType_list,AGGREGATE(15,6,ROW(BevType_list)-ROW(Setup!$D$1)/(Type_list=BevSelected),ROWS(Setup!$D$2:D17))),0) F18 =IFERROR(INDEX(BevType_list,AGGREGATE(15,6,ROW(BevType_list)-ROW(Setup!$D$1)/(Type_list=BevSelected),ROWS(Setup!$D$2:D18))),0) F19 =IFERROR(INDEX(BevType_list,AGGREGATE(15,6,ROW(BevType_list)-ROW(Setup!$D$1)/(Type_list=BevSelected),ROWS(Setup!$D$2:D19))),0) F20 =IFERROR(INDEX(BevType_list,AGGREGATE(15,6,ROW(BevType_list)-ROW(Setup!$D$1)/(Type_list=BevSelected),ROWS(Setup!$D$2:D20))),0) F21 =IFERROR(INDEX(BevType_list,AGGREGATE(15,6,ROW(BevType_list)-ROW(Setup!$D$1)/(Type_list=BevSelected),ROWS(Setup!$D$2:D21))),0) F22 =IFERROR(INDEX(BevType_list,AGGREGATE(15,6,ROW(BevType_list)-ROW(Setup!$D$1)/(Type_list=BevSelected),ROWS(Setup!$D$2:D22))),0) F23 =IFERROR(INDEX(BevType_list,AGGREGATE(15,6,ROW(BevType_list)-ROW(Setup!$D$1)/(Type_list=BevSelected),ROWS(Setup!$D$2:D23))),0) F24 =IFERROR(INDEX(BevType_list,AGGREGATE(15,6,ROW(BevType_list)-ROW(Setup!$D$1)/(Type_list=BevSelected),ROWS(Setup!$D$2:D24))),0) F25 =IFERROR(INDEX(BevType_list,AGGREGATE(15,6,ROW(BevType_list)-ROW(Setup!$D$1)/(Type_list=BevSelected),ROWS(Setup!$D$2:D25))),0) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Workbook Defined Names Name Refers To BevSelected =Pulldown!$B$3 BevType_list =OFFSET(Setup!$D$1,1,0,MATCH("zzzzzzzzzz",Setup!$D:$D)-ROW(Setup!$D$1),1) Type_list =OFFSET(Setup!$D$1,1,1,MATCH("zzzzzzzzzz",Setup!$E:$E)-ROW(Setup!$D$1),1) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Jeffrey Mahoney]

Any questions?

 

[AnandKaushal]

thanks every one. i did it by myself.

 

[AnandKaushal]

There is an external link in my workbook and i got the link detail by running a simple macro.

sub FetchLink()
Dim aLinks As Variant
aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(aLinks) Then
Sheets.Add
For i = 1 To UBound(aLinks)
Cells(i, 1).Value = aLinks(i)
Next i
End If
end sub

and by searching the link by name, i find no result the external Link fetched by this macro.

 

[Jeffrey Mahoney]