three level dependent combobox

A

AnandKaushal

New Member
Joined
Oct 20, 2017
Messages
4
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.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Sort by date Sort by votes
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
 
Upvote 0
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.
 
Upvote 0
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
BCDEFGH

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]
[TD="align: center"]Beverage[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Type Of Beverage[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Portion[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Price[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: center"]Coffee[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: center"]Decaf Coffee[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: center"]Medium[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: center"]$4.25[/TD]

</tbody>
Pulldown​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H3[/TH]
[TD="align: left"]=IFERROR(VLOOKUP(BevSelected&","&D3&","&F3,FullPrice_list,2,FALSE),0)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Workbook Defined Names[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]BevSelected[/TH]
[TD="align: left"]=Pulldown!$B$3[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]FullPrice_list[/TH]
[TD="align: left"]=OFFSET(Setup!$J$1,1,0,MATCH("zzzzzzzzzz",Setup!$J:$J)-ROW(Setup!$J$1),2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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
BCDEFGHIJK
Beverage_listBevType_listType_listAgg_listPortionFull ListPrice
TeaGreen TeaTeaSmallTea,Green Tea,Small
CoffeeBlack TeaTeaMediumTea,Green Tea,Medium
Black Decaf TeaTeaLargeTea,Green Tea,Large
Herbal TeaTeaTea,Black Tea,Small
Black CoffeeCoffeeTea,Black Tea,Medium
Regular CoffeeCoffeeTea,Black Tea,Large
EspressoCoffeeTea,Black Decaf Tea,Small
Decaf CoffeeCoffeeTea,Black Decaf Tea,Medium
Tea,Black Decaf Tea,Large
Tea,Herbal Tea,Small
Tea,Herbal Tea,Medium
Tea,Herbal Tea,Large
Coffee,Black Coffee,Small
Coffee,Black Coffee,Medium
Coffee,Black Coffee,Large
Coffee,Regular Coffee,Small
Coffee,Regular Coffee,Medium
Coffee,Regular Coffee,Large
Coffee,Espresso,Small
Coffee,Espresso,Medium
Coffee,Espresso,Large
Coffee,Decaf Coffee,Small
Coffee,Decaf Coffee,Medium
Coffee,Decaf Coffee,Large

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] "]Black Coffee[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]$2.50[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] "]Regular Coffee[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]$3.00[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] "]Espresso[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]$3.50[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] "]Decaf Coffee[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]$2.50[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]$3.00[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]$3.50[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]$2.75[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]$3.25[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]$3.75[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]$2.50[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]$3.00[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]$3.50[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]$3.75[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]$4.25[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]$4.75[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]$3.75[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]$4.25[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]$4.75[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]$4.00[/TD]

[TD="align: center"]21[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]$4.50[/TD]

[TD="align: center"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]$5.00[/TD]

[TD="align: center"]23[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]$3.75[/TD]

[TD="align: center"]24[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]$4.25[/TD]

[TD="align: center"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]$4.75[/TD]

</tbody>
Setup​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=IFERROR(INDEX(BevType_list,AGGREGATE(15,6,ROW(BevType_list)-ROW(Setup!$D$1)/(Type_list=BevSelected),ROWS(Setup!$D$2:D2))),0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F3[/TH]
[TD="align: left"]=IFERROR(INDEX(BevType_list,AGGREGATE(15,6,ROW(BevType_list)-ROW(Setup!$D$1)/(Type_list=BevSelected),ROWS(Setup!$D$2:D3))),0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F4[/TH]
[TD="align: left"]=IFERROR(INDEX(BevType_list,AGGREGATE(15,6,ROW(BevType_list)-ROW(Setup!$D$1)/(Type_list=BevSelected),ROWS(Setup!$D$2:D4))),0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F5[/TH]
[TD="align: left"]=IFERROR(INDEX(BevType_list,AGGREGATE(15,6,ROW(BevType_list)-ROW(Setup!$D$1)/(Type_list=BevSelected),ROWS(Setup!$D$2:D5))),0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F6[/TH]
[TD="align: left"]=IFERROR(INDEX(BevType_list,AGGREGATE(15,6,ROW(BevType_list)-ROW(Setup!$D$1)/(Type_list=BevSelected),ROWS(Setup!$D$2:D6))),0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F7[/TH]
[TD="align: left"]=IFERROR(INDEX(BevType_list,AGGREGATE(15,6,ROW(BevType_list)-ROW(Setup!$D$1)/(Type_list=BevSelected),ROWS(Setup!$D$2:D7))),0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F8[/TH]
[TD="align: left"]=IFERROR(INDEX(BevType_list,AGGREGATE(15,6,ROW(BevType_list)-ROW(Setup!$D$1)/(Type_list=BevSelected),ROWS(Setup!$D$2:D8))),0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F9[/TH]
[TD="align: left"]=IFERROR(INDEX(BevType_list,AGGREGATE(15,6,ROW(BevType_list)-ROW(Setup!$D$1)/(Type_list=BevSelected),ROWS(Setup!$D$2:D9))),0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F10[/TH]
[TD="align: left"]=IFERROR(INDEX(BevType_list,AGGREGATE(15,6,ROW(BevType_list)-ROW(Setup!$D$1)/(Type_list=BevSelected),ROWS(Setup!$D$2:D10))),0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F11[/TH]
[TD="align: left"]=IFERROR(INDEX(BevType_list,AGGREGATE(15,6,ROW(BevType_list)-ROW(Setup!$D$1)/(Type_list=BevSelected),ROWS(Setup!$D$2:D11))),0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F12[/TH]
[TD="align: left"]=IFERROR(INDEX(BevType_list,AGGREGATE(15,6,ROW(BevType_list)-ROW(Setup!$D$1)/(Type_list=BevSelected),ROWS(Setup!$D$2:D12))),0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F13[/TH]
[TD="align: left"]=IFERROR(INDEX(BevType_list,AGGREGATE(15,6,ROW(BevType_list)-ROW(Setup!$D$1)/(Type_list=BevSelected),ROWS(Setup!$D$2:D13))),0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F14[/TH]
[TD="align: left"]=IFERROR(INDEX(BevType_list,AGGREGATE(15,6,ROW(BevType_list)-ROW(Setup!$D$1)/(Type_list=BevSelected),ROWS(Setup!$D$2:D14))),0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F15[/TH]
[TD="align: left"]=IFERROR(INDEX(BevType_list,AGGREGATE(15,6,ROW(BevType_list)-ROW(Setup!$D$1)/(Type_list=BevSelected),ROWS(Setup!$D$2:D15))),0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F16[/TH]
[TD="align: left"]=IFERROR(INDEX(BevType_list,AGGREGATE(15,6,ROW(BevType_list)-ROW(Setup!$D$1)/(Type_list=BevSelected),ROWS(Setup!$D$2:D16))),0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F17[/TH]
[TD="align: left"]=IFERROR(INDEX(BevType_list,AGGREGATE(15,6,ROW(BevType_list)-ROW(Setup!$D$1)/(Type_list=BevSelected),ROWS(Setup!$D$2:D17))),0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F18[/TH]
[TD="align: left"]=IFERROR(INDEX(BevType_list,AGGREGATE(15,6,ROW(BevType_list)-ROW(Setup!$D$1)/(Type_list=BevSelected),ROWS(Setup!$D$2:D18))),0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F19[/TH]
[TD="align: left"]=IFERROR(INDEX(BevType_list,AGGREGATE(15,6,ROW(BevType_list)-ROW(Setup!$D$1)/(Type_list=BevSelected),ROWS(Setup!$D$2:D19))),0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F20[/TH]
[TD="align: left"]=IFERROR(INDEX(BevType_list,AGGREGATE(15,6,ROW(BevType_list)-ROW(Setup!$D$1)/(Type_list=BevSelected),ROWS(Setup!$D$2:D20))),0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F21[/TH]
[TD="align: left"]=IFERROR(INDEX(BevType_list,AGGREGATE(15,6,ROW(BevType_list)-ROW(Setup!$D$1)/(Type_list=BevSelected),ROWS(Setup!$D$2:D21))),0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F22[/TH]
[TD="align: left"]=IFERROR(INDEX(BevType_list,AGGREGATE(15,6,ROW(BevType_list)-ROW(Setup!$D$1)/(Type_list=BevSelected),ROWS(Setup!$D$2:D22))),0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F23[/TH]
[TD="align: left"]=IFERROR(INDEX(BevType_list,AGGREGATE(15,6,ROW(BevType_list)-ROW(Setup!$D$1)/(Type_list=BevSelected),ROWS(Setup!$D$2:D23))),0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F24[/TH]
[TD="align: left"]=IFERROR(INDEX(BevType_list,AGGREGATE(15,6,ROW(BevType_list)-ROW(Setup!$D$1)/(Type_list=BevSelected),ROWS(Setup!$D$2:D24))),0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F25[/TH]
[TD="align: left"]=IFERROR(INDEX(BevType_list,AGGREGATE(15,6,ROW(BevType_list)-ROW(Setup!$D$1)/(Type_list=BevSelected),ROWS(Setup!$D$2:D25))),0)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Workbook Defined Names[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]BevSelected[/TH]
[TD="align: left"]=Pulldown!$B$3[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]BevType_list[/TH]
[TD="align: left"]=OFFSET(Setup!$D$1,1,0,MATCH("zzzzzzzzzz",Setup!$D:$D)-ROW(Setup!$D$1),1)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Type_list[/TH]
[TD="align: left"]=OFFSET(Setup!$D$1,1,1,MATCH("zzzzzzzzzz",Setup!$E:$E)-ROW(Setup!$D$1),1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
There is an external link in my workbook and i got the link detail by running a simple macro.

Code: 
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A
G
  • Question Question
Help me with this!
Replies
1
Views
347
bobaol
B
A
  • Solved
Calendar issue
Replies
1
Views
2K
AndyEd
A
H
  • Question Question
Complex reconciliation problem
Replies
1
Views
498
Fluff
Fluff
C
  • Question Question
Formula to find Max number of items in orders
Replies
1
Views
680
fjns
F

Forum statistics

Threads
1,223,910
Messages
6,175,316
Members
452,634
Latest member
cpostell

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top