Excel 2007 : Restaurant Menu Problem

**WelshPunk** · 09-17-2011, 11:02 AM

Hi clever ones

I have a spreadsheet (2007) with some conditional formating in a list which details items on a menu in a restaurant. If the user selects any of the steaks, I want to show an extra column adjacent with options for Rare, Medium Rare or Well Done. But I dont want it displayed if users choose non steak meals

Any help is greatful

Regards

**mrice** · 09-17-2011, 11:25 AM

Can you post an example showing what 'selected' means and where you want the new column to appear?

**JBeaucaire** · 09-17-2011, 11:37 AM

These are called dependent drop down lists.

I imagine on your "database" sheet you will list your meals. In an adjacent column next to each meal you could put the name of a "required" set of questions, a name that matches a "named range" you've given to those option elsewhere in your workbook.

So, if on a LISTS sheet you listed Rare, Medium Rare, Medium, MedWell, Well in cells A1:A5, you would highlight A1:A5 then Insert>Name>Define and name those cells CookTemp.

You could create other similar lists on that same sheet.

Then on your MEALS sheet, let's say you listed Entrees in column A. So A2 says "Ribeye Steak". In B2 you would put the name of the "required" instructions CookTemp. Repeat that for any other meals, Entree in column A and CookTemp in Col B if it's required. Probably should put the meal price in column C?

Now highlight columns A:C on this sheet then Insert>Name>Define and give this whole table a name ofItemTable.

Now highlight JUST column A with your Entree names and Insert>Name>Define this columns Entrees, and change the RefersTo: to read:

=OFFSET($A$1,,,COUNTA($A:$A),)

...now the Entrees list is a self-expanding dynamic list,

Now on your MAIN sheet, let's put a drop down in column to make Entree choice. Select A2:20 and open the Data Validation settings, set it to:

Allow: List
Source: =Entrees

Now we've reached your original question, how to make column B show the cooking instructions "IF" needed.

First, let's create a NAMED FORMULA, to make this easy to use. This step is critical, you have to click on the FIRST cell that will use this named formula, so click on B2 before you do anything else.

Now open Insert>Name>Define and create a name Required and the RefersTo: should read: =VLOOKUP(B2, ItemsTable, 2, 0).

Now in A2, select a "meat entree" like Ribeye.
Now back in B2, open the Data Validation settings and use:

Allow: List
Source: =INDIRECT(Required)

And you should be done. B2 will now show the drop down of cooking instructions if you enter an Entree that has a required list in the ItemsTable.

Give it a go.

**WelshPunk** · 09-17-2011, 11:43 AM

I have a worksheet that I use for named ranges. One range is called MainMeals and contains three or four items 8 oz Steak, Rib Eye Steak, Spag Bol, Curry etc

On another sheet called choices I created a conditional list that uses the named range MainMeals.

Here is the Columns:

Main Meals Desserts

The Desserts column works in the same way.
If the user selects any of the steak options in MainMeals list , I would like an imputbox to prompt them to select how their steak should be cooked. And then to enter that info into the column adjacent to the desserts column.

Regards Phill

**JBeaucaire** · 09-17-2011, 12:31 PM

You'll need to add more information to your Lists sheet, like what I suggested above.

**WelshPunk** · 09-17-2011, 02:15 PM

Hi JBeaucare

Im getting lost in the instruction somewhere sorry... My first worksheet I renamed List and on this sheet created a name range for all the cooking types rare, med rare etc. This is the only named range on this page.

On the next sheet which I called Meals I entered in the A Column a Main Meal heading in A1 and from A2 down entered a variety of main meals. I selected A:C and named the three columns ItemTable

Then Selected the first column and named the range Entrees with =OFFSET($A$1,,,CountA($A:$A),)

Then on the same Meals sheet create a dropdown validation list for the column A with source = Entrees

Then click into B2 and create the Named Formula Required with = VLOOKUP(B2,ITemTable,2,0)

Then select in the A column an item e.g. Rib Eye Steak

Then in B2 create a dropdown validation list and set the Source to = INDIRECT(Required)

Its this last step that I get an error with

Regards Phill

**WelshPunk** · 09-17-2011, 04:29 PM

Im sorry but this doesnt seem to work.....
Two Sheets one named Lists and the other Meals.

Lists contains two columns of data in A and B

Macaroni Cheese Rare
Lamb Dinner Medium Rare
Sirloin Steak Medium
Rib Eye Steak Medium Well
Curry Well Done
Chicken Provencal Crispy

I Highlighted col A:B and named this range ItemsTable
Then Highlight just the Meals in A and named the range Entrees and Refers to
=OFFSET($A$1,,,COUNTA($A:$A),0)

Then swithching to the Meals sheet I create a validation list source = Entrees which allows me to add meals.

Then Select B2 and create the named formula Required refers to =VLOOKUP(B2,ItemsTable,2,0)

Then create a validation dropdown list source = INDIRECT(Required)

This last step I get an error message 'The Source currently evaluates to an error, do you want to continue?

Spent a long time ensuring that I was copying out the suggestion. Any ideas please?

Regards Phill

**JBeaucaire** · 09-18-2011, 09:57 AM

My apologies. The named formula (while B2 is selected) should be:

=INDIRECT(VLOOKUP(A2, ItemTable, 2, 0))

And the DV formula for B2 is just:

=Required

See attached.