Category: Excel Templates

There is an Excel weekly meal planner on the Contextures website, in which you can select meal items, and create a weekly shopping list.

Shopping List Problems

In December, I added an online recipe selector, created by Jimmy Peña, and described the new feature in a blog post.

This weekend, Alyssa pointed out a problem — if you select a meal item twice, it’s only added to the shopping list once.

That could cause problems, if you run out of food on Friday, and have hungry and cranky children waiting for their dinner. Thanks Alyssa!

Fix the List

To show the correct quantities in the shopping list, I changed the heading in the original quantity column, from Qty to Meal Qty.

Then, I added a new column, with the heading Qty, and a formula to multiply the Meal Qty by the List qty.

The formula in cell H2 multiplies the Meal Qty by the List Qty:

• =C2*G2

That should prevent any food shortages at the end of the week!

Download the Updated Excel Meal Planner

You can see the full details for the Excel Weekly Meal Planner on the Contextures website, and download an updated copy to help plan your meals.
____________

The problem with putting a lovely red geranium on your table is that you can end up remodelling the kitchen! (Do you remember that magic geranium fable?)

Excel Weekly Meal Planner

Anyway, a while ago, I posted my Excel Weekly Meal Planner, which let you select meals for each weeknight, and print a grocery shopping list.

All was well in my kitchen, as I happily planned my meals with that useful workbook.

Then, JP from Code for Excel and Outlook, sent me a “geranium” — a copy of my Excel Christmas Planner, with a fancy new Excel Recipe Selector worksheet.

JP’s workbook has code that looks up recipes via web API, and returns the result to Excel.

Find a Recipe

For example, if you’re tired of steamed carrots, you can enter “Carrots” in the Recipes worksheet, and find more exotic recipes online.

Thanks JP! Your enhancement works great, and now it will be easy to spice up those holiday meals.

Remodelling the Excel Weekly Meal Planner

Yes, JP’s Recipe Selector makes the Holiday Dinner Planner better, but it made my Excel Weekly Meal Planner look shabby!

It needed a recipe selector too. In his blog post, JP showed the code for using the API, and had a link to another function that’s required.

Add a Recipes Sheet

So, I copied the Recipes sheet from JP’s workbook, and inserted it into my weekly meal planner file. It worked great in its new location, and I just had to change a couple of references in the code.

But…now the rest of the weekly meal planner looked a bit run down, so I decide to remodel it. After a couple of coats of paint, a new backsplash and a shiny new pivot table, it’s ready for the open house.

Get the Update Version

You can tour the remodelled version on the Excel Weekly Meal Planner page on the Contextures website, and download a copy to help plan your meals.

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.

___________

Last year, I posted a link to my Excel Christmas planner, that includes a scheduler for holiday meals.

Despite the rude comments from my Excel buddies (OCD? Torturing guests with pivot tables?), I still use that planner to stay on schedule.

Maybe those doubters go out for their holiday meals, so they don’t have to worry about planning!

Black Friday Sales Planner

Even if you don’t have to plan the holiday dinner, you might want to plan your Black Friday shopping trip, or compare gift prices at different stores.

In the latest version of the Excel Christmas Planner there’s a Black Friday worksheet.

Enter Store Names at the top of the table, then enter product info and prices in the rows below.

Find Best Prices

The worksheet calculates which store has the best price for each item, and which store has the most deals.

Note: If prices are the same at multiple stores, the first store will be shown in the “Best Price” column.

With this handy Excel Holiday Shopping worksheet, you’ll know where to start your shopping blitz, so you get the most for your money.

And that’s what the holidays are all about, right? 😉

Happy Thanksgiving!
__________

There’s an Excel holiday dinner planner on the Contextures website, and I’ll need that in about a month, when Thanksgiving arrives. In the meantime though, there are lots of daily meals to plan.

Continue reading “Plan Weekly Meals in Excel”

I’d need wider columns to hold my golf scores, but this Excel template for golf scores might help you keep track of your annual progress.

Annual Summary

There’s also an annual summary sheet, that calculates your average score and best score for the year.

Conditional Formatting for Scores

The golf scores template uses conditional formatting to highlight the holes where you shot par or below par, so you can see at a glance how things are going.

You enter the course pars on the Summary sheet, and the conditional formatting is on the Scores sheet.

Name the Cell

Because conditional formatting won’t allow you to refer to cells on another sheet, I named cell B8 as CoursePar, and use that name in the conditional formatting formulas.

For example, the formula for par is:

• =B2=OFFSET(CoursePar,0, COLUMN()-1)

Download the Golf Scores Template

To download the Golf Scores template, go to the Excel Template – Golf Scores page on my Contextures website.

he zipped Excel workbook does not contain any macros — Excel formulas so all the work, after you enter the scores.

More Golf With Excel

If you’re organizing a golf tournament, you can organize the tee off times with another one of my Excel Golf templates.

Go to the Golf Tee Off Times page on my Contextures site, to download the Excel workbook.

The video below shows how the Tee Off Times file works.

______________

Don’t get too close to this blog today – I’ve got a miserable cold, and wouldn’t want you to catch it!

Roger Govier has created an Excel file to track your medical treatments, for people who have varying dosages or injection sites. It won’t help me feel better, but it might help you, or someone you know.

Enter the Treatment Sequence

Roger’s workbook has two worksheets – Calendar and Setup. Start on the Setup sheet, where you’ll enter the medication sequence.

First, enter your daily dosages in the Treatment List column.

• For example, someone who’s taking a medication might be prescribed to take daily doses of 2 mg, 2mg, 3mg, 2mg, 5mg and then back to the start of the sequence.
• They would fill in 5 cells in the Treatment List, shown in section 1 in the screenshot below.

Next, after you’ve entered the daily dosage sequence in the Treatment List, click the Fill Treatment Column button (number 2 in the screenshot above).

A macro runs, which clears the Treatments column (number 3 in the screenshot) and then fills it again, based on the treatment sequence that you entered.

View the Treatment Calendar

After setting up the Treatment Sequence, go to the Calendar sheet, which shows a monthly calendar.

At the top of the worksheet, select a year and month from the drop down lists.

Then, from the Start Treatment drop down, select the starting treatment for the selected month. In this example, the previous month ended with a 3mg dosage, so the fourth dosage, 2mg, is selected.

The calendar automatically adjusts to show the new treatment schedule.

Download the Sample Treatment Workbook

Click here to download Roger’s Sample Excel Treatment Workbook. It’s a zipped file in Excel 2003 format, and contains a macro.

You’ll have to enable macros to run the FillColumn macro on the Setup sheet.

Healing Music

If you’re sick too, this video, with the soothing sounds of Peggy Lee, might help you feel better, and get rid of your fever.

______