Want to Create a Catering menu with Auto Populating Price

[ketihd203]

Hello All!

I am new to this great forum. I wish I'd found it sooneer, would have saved me a lot of headaches.

My sister owns a catering service, and she asked me to create a menu for a weekly event. She's not very computer literate, so I wanted to create something that was very easy for her to use and update. This is what I came up with.

There are over 45 food, desserts, and drink choices on my sister's full menu, but she picks between 8 and 15 for each weekly event, and those choices change every week. Using the sample below, I created a dropdown list from the Menu column. What I would like to happen is for her to select her menu choice from the dropdown list, and have the corresponding price column auto populate.

<TABLE style="WIDTH: 211pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=281 x:str><COLGROUP><COL style="WIDTH: 161pt; mso-width-source: userset; mso-width-alt: 7826" width=214><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2450" width=67><TBODY><TR style="HEIGHT: 20.25pt; mso-height-source: userset" height=27><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 161pt; HEIGHT: 20.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl26 height=27 width=214>Menu</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 50pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25 width=67 x:str="Price"> Price </TD></TR><TR style="HEIGHT: 17.25pt; mso-height-source: userset" height=23><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 161pt; HEIGHT: 17.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl27 height=23 width=214>Baked Beans</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl24 x:num="1"> $ 1.00 </TD></TR><TR style="HEIGHT: 17.25pt; mso-height-source: userset" height=23><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 161pt; HEIGHT: 17.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 height=23 width=214>BBQ Wings (3) w/Mac & Cheese and String Beans or Salad</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl24 x:num="7"> $ 7.00 </TD></TR><TR style="HEIGHT: 17.25pt; mso-height-source: userset" height=23><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 161pt; HEIGHT: 17.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl27 height=23 width=214>Beef Stew</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl24 x:num="2"> $ 2.00 </TD></TR><TR style="HEIGHT: 17.25pt; mso-height-source: userset" height=23><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 161pt; HEIGHT: 17.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl27 height=23 width=214>Bolona Sandwich</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl24 x:num="2"> $ 2.00 </TD></TR><TR style="HEIGHT: 17.25pt; mso-height-source: userset" height=23><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 161pt; HEIGHT: 17.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl27 height=23 width=214>Fish & Fries</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl24 x:num="5"> $ 5.00 </TD></TR><TR style="HEIGHT: 17.25pt; mso-height-source: userset" height=23><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 161pt; HEIGHT: 17.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl27 height=23 width=214>Fish Sandwich</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl24 x:num="4"> $ 4.00 </TD></TR><TR style="HEIGHT: 17.25pt; mso-height-source: userset" height=23><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 161pt; HEIGHT: 17.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl27 height=23 width=214>French Toast</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl24 x:num="1"> $ 1.00 </TD></TR><TR style="HEIGHT: 17.25pt; mso-height-source: userset" height=23><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 161pt; HEIGHT: 17.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl27 height=23 width=214>Fries</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl24 x:num="2"> $ 2.00 </TD></TR><TR style="HEIGHT: 17.25pt; mso-height-source: userset" height=23><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 161pt; HEIGHT: 17.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl27 height=23 width=214>Grilled Cheese</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl24 x:num="3"> $ 3.00 </TD></TR><TR style="HEIGHT: 17.25pt; mso-height-source: userset" height=23><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 161pt; HEIGHT: 17.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl27 height=23 width=214>Grilled Chicken Salad</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl24 x:num="5"> $ 5.00 </TD></TR><TR style="HEIGHT: 17.25pt; mso-height-source: userset" height=23><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 161pt; HEIGHT: 17.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl27 height=23 width=214>Hot Dog</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl24 x:num="3"> $ 3.00 </TD></TR><TR style="HEIGHT: 17.25pt; mso-height-source: userset" height=23><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 161pt; HEIGHT: 17.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl27 height=23 width=214>Mixed Green Salad</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl24 x:num="2"> $ 2.00 </TD></TR><TR style="HEIGHT: 17.25pt; mso-height-source: userset" height=23><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 161pt; HEIGHT: 17.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl27 height=23 width=214>Pancakes</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl24 x:num="1"> $ 1.00 </TD></TR><TR style="HEIGHT: 17.25pt; mso-height-source: userset" height=23><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 161pt; HEIGHT: 17.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl27 height=23 width=214>Turkey Sandwich</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl24 x:num="2"> $ 2.00 </TD></TR></TBODY></TABLE>
This sounded simple when I first thought of it. I tried a vlookup, an hlookup, an if function, and a choose function, but I could not get any of them to work. I'm sure I missed something simple, but I just can't figure out what. Any help you can provide would be greatly appreciated.

 

[Trevor G]

Welcome to the forum,

Take a look here, sample of table

Sheet2

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 371px"><COL style="WIDTH: 43px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt; FONT-WEIGHT: bold">Menu</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt; FONT-WEIGHT: bold">Price </TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt">Baked Beans</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt">$ 1.00 </TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt">BBQ Wings (3) w/Mac & Cheese and String Beans or Salad</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt">$ 7.00 </TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt">Beef Stew</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt">$ 2.00 </TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt">Bolona Sandwich</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt">$ 2.00 </TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt">Fish & Fries</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt">$ 5.00 </TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt">Fish Sandwich</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt">$ 4.00 </TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt">French Toast</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt">$ 1.00 </TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt">Fries</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt">$ 2.00 </TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt">Grilled Cheese</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt">$ 3.00 </TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt">Grilled Chicken Salad</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt">$ 5.00 </TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt">Hot Dog</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt">$ 3.00 </TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt">Mixed Green Salad</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt">$ 2.00 </TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt">Pancakes</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt">$ 1.00 </TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt">Turkey Sandwich</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt">$ 2.00 </TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

I have named ranges for MenuFood and Prices, then used a Validation for Menu Items and used Index and Match to get the price.

See second table.

Sheet2

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 368px"><COL style="WIDTH: 45px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Selected Menu</TD><TD>Cost</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>Fries</TD><TD>$ 2.00 </TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>BBQ Wings (3) w/Mac & Cheese and String Beans or Salad</TD><TD>$ 7.00 </TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>Fish & Fries</TD><TD>$ 5.00 </TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>French Toast</TD><TD>$ 1.00 </TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>French Toast</TD><TD>$ 1.00 </TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>D2</TD><TD>=INDEX(Price,MATCH(C2,MenuFood,FALSE),1)</TD></TR><TR><TD>D3</TD><TD>=INDEX(Price,MATCH(C3,MenuFood,FALSE),1)</TD></TR><TR><TD>D4</TD><TD>=INDEX(Price,MATCH(C4,MenuFood,FALSE),1)</TD></TR><TR><TD>D5</TD><TD>=INDEX(Price,MATCH(C5,MenuFood,FALSE),1)</TD></TR><TR><TD>D6</TD><TD>=INDEX(Price,MATCH(C6,MenuFood,FALSE),1)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

So you could have them on separate sheets. Hope this helps...

 

[ketihd203]

Thank you, that works perfectly!!

 

[Trevor G]

Pleased to read you have a working solution.