Search Contextures Sites

 

Contextures
Excel news
by email

 

 

30 Excel Functions in 30 Days

 

 

Learn how to create Excel dashboards.

 

Excel Weekly Meal Planner

Thanks to Jimmy Peña, who created the Recipe Selector tool in the meal planner.

Select Recipes
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.

Select Recipes

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.

Excel recipes finder

Enter the List of Meal Items

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.

meal list

List the Meal Ingredients

After you have entered a list of meal items, enter the main ingredients for each item on the Meal_Ingredients worksheet.

  1. There is a data validation drop down in column B, where you can select the name of one of your meal items.
  2. In column C, enter the quantity required, and in column D, enter the unit, such a can, cup or lb.
  3. In column E, enter the name of the ingredient.
  4. 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.

meal ingredients

Select the Weekly Meals

After you have entered all the ingredients, you're ready to start the weekly meal planning.

  1. 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.

    select meals

Create the Weekly Shopping List

After you select each day's meals, click the Shopping List button at the top left of the WeekdayMeals sheet.

shopping list button

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.

criteria range

Print the Shopping List

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.

shopping list

Watch the Excel Weekly Meal Planner Video

To see a quick overview of how the Excel Weekly Meal Planner works, you can watch this short video.

Weekly Meal Planner VBA Code

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

Download the Excel Weekly Meal Planner

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.

 

   

 

Privacy Policy

 

Contextures Inc., Copyright ©2014
All rights reserved.

 

Last updated: April 13, 2014 10:13 AM