08/17/2017 – Grocery list from meal planner

I’m usually the meal planner in my house. It’s not due to my wife not being able to do it, but rather that she does not like to and I do. I’m also someone who enjoys making spreadsheets for everything. So, I thought it may be fun to create a spreadsheet that would allow me to plan dinners for the week. And, the spreadsheet would generate a list of ingredients for the dinners, so I would know what to buy. Here is how I did it.

First thing was to figure out a list of dinner options. For this example, I will use:

  • Cheeseburgers
  • Spaghetti
  • Chili dogs
  • Nachos

Now that I have a list of meals, the next step was to figure out the ingredients. For my spreadsheet, I kept is simple. I just wanted a grocery list, not a recipe. For example, for Cheeseburgers, I put down:

  • Frozen hamburger patties
  • Hamburger buns
  • Cheddar cheese
  • Tomato
  • Onion
  • Lettuce
  • Mayonaise
  • Mustard
  • Ketchup

Now that I have the data, it was time to create the spreadsheet.

For this tutorial, I created my spreadsheet in Google Docs. The formulas that I am using should work in any spreadsheet program. I tested them out in Excel 2013 before using them in Google Docs.

Also, for this tutorial, I am only using one tab on workbook. In the version I use privately, I am using three separate tabs. I recommend arranging things in a way that works for you.

On the spreadsheet (columns F-I), I first fill in the name of each meal, then list the ingredients underneath the name. For example, here’s “cheeseburgers”:

Cheeseburgers
Frozen hamburger patties
Hamburger buns
Cheddar cheese
Tomato
Onion
Lettuce
mayonnaise
Mustard
Ketchup

When filling in meals with a space, like “Chili dogs”, I use an underscore in place of the space. For example, “Chili_dogs”. We are going to use these in an drop-down list later, which will not allow spaces.img1

Once the meal names and ingredients are added, it is time to make them into lists. In Google Docs, it is as simple as selecting all the cells you want on the list, right-click, then select, “Define named range”. For Excel, it is almost the same, but the option is, “Define name”. I make the meal names into a one list and call it, “Meals”. Then, I make each list of ingredients for each meal into a separate list. So, the list of ingredients for the cheeseburgers is a list, called, “Cheeseburgers”.

The next thing I created was the days of the week (Monday, Tuesday, Wednesday, etc.), listed top to bottom (column A, rows 2-8). I selected the cell next to the first day (cell B2: “Monday”), right-clicked, and selected “Data validation”. I made sure, “List from a range” was selected and typed “Meals” in the box. Then, I copied that cell, selected the cells next to rest of the days (“Tuesday”, “Wednesday”, etc.), right-clicked, and selected, “Paste special”, and “Paste data validation only”. This made it so I could choose one of the meals from a drop-down menu for each day.

img2

For the next part, I needed to know how many items were in the longest ingredients list. For the example, “Cheeseburgers” had the longest with nine items. I then put the names, “Shopping List” and each day of the week (Monday, Tuesday, etc.) across the top of eight columns (columns L-R). Under the name of the day of the week (in row 2), I put “=”, then selected the cell containing the drop-down menu next to that day (from column B). This would make it so when I selected a meal next to a day, it would also populate this cell. For example, if I selected “Chili_dogs” for Monday in cell B2, it would also populate cell L2 with “Chili_dogs”.img3

In the row under the cells that I put the “=” (row 3), I put the formula, “=INDIRECT(L2)”, “=INDIRECT(M2)”, “=INDIRECT(N2)”, etc. The “L2”, “M2”, etc. refers to the cell above it. This would make it so when a meal was selected next to the day of the week and that name would populate in row 2, the ingredients for that meal would populate starting in row 3. or example, if I selected “Chili_dogs” for Monday in cell B2, it would populate cell L2 with “Chili_dogs”, and then, starting with cell L3, the ingredients for “Chili_dogs” would populate.

**Note** For Excel, I had to change the formula a little. Google Docs displayed the entire ingredients list, but Excel only displayed one row. So, I changed “=INDIRECT(L2)” to “=INDIRECT(L$2)” and copy and paste the formula down fifteen rows.

Remember when I said that I needed to know how many items were in the longest ingredients list? Well, this is where that knowledge will come in. In my example. “Cheeseburgers” had the longest ingredients list with nine items. So, starting with cell L3, I counted down ten rows to cell L12. I selected all those cells, right-clicked, then selected, “Define named range”. I named the range, “List1”. Then I repeated the same process for rest of the columns, resulting in “List1” to “List7”.

The code that I will use next, I got from Get Digital Help.com.

Lastly, we are ready to generate the shopping list. I create a column with the name “Shopping List” on the first row (D1 in the example). On the second row, I put the following code:

=IFERROR(INDEX(List1, (ROWS(D1:$D$1))),
IFERROR(INDEX(List2, ROWS(D1:$D$1)-ROWS(List1)),
IFERROR(INDEX(List3, ROWS(D1:$D$1)-ROWS(List1)-ROWS(List2)),
IFERROR(INDEX(List4, ROWS(D1:$D$1)-ROWS(List1)-ROWS(List2)-ROWS(List3)),
IFERROR(INDEX(List5, ROWS(D1:$D$1)-ROWS(List1)-ROWS(List2)-ROWS(List3)-ROWS(List4)),
IFERROR(INDEX(List6, ROWS(D1:$D$1)-ROWS(List1)-ROWS(List2)-ROWS(List3)-ROWS(List4)-ROWS(List5)),
IFERROR(INDEX(List7, ROWS(D1:$D$1)-ROWS(List1)-ROWS(List2)-ROWS(List3)-ROWS(List4)-ROWS(List5)-ROWS(List6)),
“”)))))))

After typing the last “)”, I press, at the same time, “CTRL”, “Shift”, and “Enter”. In Google Docs, it will add “ArrayFormula” and an extra “)”. In Excel, it will add “{” and “}”. I then copy the code, and paste it down the column for seventy rows (10 ingredients * 7 days of the week).

Currently, I have not figured out how to automatically remove all the empty cells or duplicates from the “Grocery List” row. I know it is possible to copy and paste-special the values to another row, where I can remove the cells and duplicates.

Advertisements

About Thomas J. Brown

I am the last true Saiyan...wait, that can't be right...
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s