Search Contextures Sites
Excel Weekly Meal Planner
Thanks to Jimmy Peña, who created the Recipe Selector tool in the meal planner.
Enter the List of Meal Items
List the Meal Ingredients
Select the Weekly Meals
Create the Weekly Shopping List
Print the Shopping List
Watch the Excel Weekly Meal Planner Video
Weekly Meal Planner VBA Code
Download the Excel Weekly Meal Planner
When the kids are in school, and everyone is involved in evening activities, it can be tough to get your family meals organized.
To help you make the job easier, you can use this Excel weekly meal planner. Enter your favourite meals, list their basic ingredients, and click a button to create a shopping list for a week's meals.
Note: For special occasions, there's an Excel holiday dinner planner that you can use to organize a meal for a large group, or a fancy dinner for friends.
The first step is to select recipes for your weekly meals, either from your own collection, or on the Recipes sheet in the Excel Weekly Meal Planner.
Enter an ingredient name in cell A2, then click the Get Recipes button. A macro runs, and pulls a list of recipes for the selected ingredient. To see one of the recipes, click the link.
The next step is to list your family's favourite dishes on the Meal_Items worksheet. There are a few sample items in the Excel weekly meal planner. You can add new items to the list, or replace the existing meals with your family favourites.
In column B, enter the name of the dish, and in column C you can enter a link to a recipe online, or type a cookbook name and page number.
After you have entered a list of meal items, enter the main ingredients for each item on the Meal_Ingredients worksheet.
- There is a data validation drop down in column B, where you can select the name of one of your meal items.
- In column C, enter the quantity required, and in column D, enter the unit, such a can, cup or lb.
- In column E, enter the name of the ingredient.
- Finally, in column F, enter the category for the ingredient, to help you make a shopping list.
Note: There are formulas in columns G and H, which are used to create the shopping list, so don't type in those columns.
After you have entered all the ingredients, you're ready to start the weekly meal planning.
- On the WeekdayMeals sheet, select up to 3 items for each weekday meal, by using the data validation drop down lists in columns C:E.
After you select each day's meals, click the Shopping List button at the top left of the WeekdayMeals sheet.
This runs a macro, which uses an advanced filter to copy all the ingredients for the selected meals to the ShoppingList sheet, and sorts the shopping list by ingredient name.
As you select each day's meal, that meal's ingredients are marked with a 1 in column G, on the Meal_Ingredients worksheet. On the Meal_Items sheet, in cells G1:G2, there is a criteria range that's used in the advanced filter. It checks for ingredients that are marked with a 1 (or higher) in column G.
After the shopping list has been created, the items are summarized in an Excel Pivot Table. You can print the list, add any other grocery items that you need, and then head to the grocery store.
To see a quick overview of how the Excel Weekly Meal Planner works, you can watch this short video.
Shown below is the code for the weekly meal planner sample file.
- The first macro -- CreateShoppingList -- goes on a regular code module.
- The remaining code goes on the sheet module for the Recipes worksheet
Put this code on a regular code module. It creates the shopping list, based on the selected meals.Sub CreateShoppingList() Dim wsShop As Worksheet Dim wsIngred As Worksheet Dim wsList As Worksheet Dim wsItems As Worksheet Dim wsPrint As Worksheet Set wsShop = Sheets("ShoppingList") Set wsIngred = Sheets("Meal_Ingredients") Set wsItems = Sheets("Meal_Items") Set wsPrint = Sheets("ShopListPrint") wsIngred.Columns("B:J").AdvancedFilter _ Action:=xlFilterCopy, _ CriteriaRange:=wsItems.Range("CritShop"), _ CopyToRange:=wsShop.Range("ExtShop"), _ Unique:=False With wsShop .Cells(1, 2).CurrentRegion.Sort _ Key1:=.Range("C1"), _ Order1:=xlAscending, _ Header:=xlYes End With With wsPrint .PivotTables(1).PivotCache.Refresh .Activate End With End Sub
Put this code on the Recipes sheet code module (right-click the Recipes sheet tab, click View Code, and paste the code there)Option Explicit Private Sub CommandButton1_Click() Call GetRecipes End Sub '------------------------------ Sub GetRecipes() On Error GoTo ErrorHandler Const RangeToImport As String = "$A$10" ' delete existing xml map (if any) If ActiveWorkbook.XmlMaps.Count > 0 Then ActiveWorkbook.XmlMaps.Item(1).Delete End If ' clear out range to prep for new import Range(RangeToImport).CurrentRegion.Cells.Clear ' import web XML to destination range ActiveWorkbook.XmlImport URL:= _ "http://www.recipepuppy.com/api/?q=" _ & URLEncode(Range("SelItem").Value) _ & "&format=xml", ImportMap:=Nothing, _ overwrite:=True, Destination:=Range(RangeToImport) ProgramExit: Exit Sub ErrorHandler: MsgBox Err.Number & " - " & Err.Description Resume ProgramExit End Sub '------------------------------ Public Function URLEncode(EncodeStr As String) As String ' http://www.freevbcode.com/ShowCode.Asp?ID=5137 Dim i As Integer Dim erg As String erg = EncodeStr ' *** First replace '%' chr erg = Replace(erg, "%", Chr(1)) ' *** then '+' chr erg = Replace(erg, "+", Chr(2)) For i = 0 To 255 Select Case i ' *** Allowed 'regular' characters Case 37, 43, 48 To 57, 65 To 90, 97 To 122 Case 1 ' *** Replace original % erg = Replace(erg, Chr(i), "%25") Case 2 ' *** Replace original + erg = Replace(erg, Chr(i), "%2B") Case 32 erg = Replace(erg, Chr(i), "%20") Case 3 To 15 erg = Replace(erg, Chr(i), "%0" & Hex(i)) Case Else erg = Replace(erg, Chr(i), "%" & Hex(i)) End Select Next URLEncode = erg End Function
You can download a copy of the Excel Weekly Meal Planner and add your own meals and ingredients. The file is in Excel 2007 format, and is zipped. After you unzip the file and open it, enable macros, so you can create the shopping list.
Contextures Inc., Copyright ©2013
All rights reserved.
Last updated: September 21, 2013 9:14 AM