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 |
---|
Beverage_list | BevType_list | Type_list | Agg_list | Portion | Full List | Price | | | | |
Tea | Green Tea | Tea | Small | Tea,Green Tea,Small | | | | | | |
Coffee | Black Tea | Tea | Medium | Tea,Green Tea,Medium | | | | | | |
Black Decaf Tea | Tea | Large | Tea,Green Tea,Large | | | | | | | |
Herbal Tea | Tea | Tea,Black Tea,Small | | | | | | | | |
Black Coffee | Coffee | Tea,Black Tea,Medium | | | | | | | | |
Regular Coffee | Coffee | Tea,Black Tea,Large | | | | | | | | |
Espresso | Coffee | Tea,Black Decaf Tea,Small | | | | | | | | |
Decaf Coffee | Coffee | Tea,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]