|
|
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
Continue reading Excel Weekly Meal Planner Update
An Excel scroll bar can be used for practical (and sometimes boring) things, like testing the effect of price changes, or adjusting a chart's date range. But this is the festive season, so let's use a scroll bar for something more, well, festive!
Trim the Tree
In this example, instead of accounting and finance, you'll see how to use an Excel scroll bar to decorate a Christmas tree, without macros. Unfortunately, it can't make hot chocolate or eggnog, so you'll need to provide your own.
It's not just for the holiday season though -- the sample file has useful features that you can adapt to other workbooks too:
Scroll bar lets users change a number quickly and easily A text box that displays a changing message based on VLOOKUP formula conditional formatting shows hidden cells when target number is reached named ranges make it easy to work with specific cells
Continue reading Use Excel Scroll Bar to Trim Christmas Tree
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?)
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.
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
Continue reading Excel Weekly Meal Planner With Recipe Selector
Last December, I posted an Excel Advent Calendar, and it was surprisingly popular, throughout the year.
Your kids are probably much smarter this year, so I've created a new version for this year. Instead of a number on each square, there's a simple Excel formula. Each formula calculates to a number between 1 and 24.
Each day, click on the shape with the formula that calculates to that day number, to reveal the Christmas picture behind it. For example, on December 1, click the shape at the top left, with the formula: = 2 – 1
You can cover the shape again, by clicking on the border of the revealed shape.
And since you might like to peek into the squares ahead of time, there's built in cheat protection, so you can't uncover the days before they occur.
Change the Formulas
If your kids are extra
Continue reading Excel Advent Calendar for 2010
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. The worksheet calculates which store has the best price for each item, and which store has the most deals. Note: If prices are the
Continue reading Holiday Preparations for Excel Overachievers
Happy Canadian Thanksgiving! You probably have your own spreadsheet to organize the meal, but you can download my Excel Holiday Dinner Planner, if you don't have one of your own. The dinner planner has data validation cells, where you can select a start time, or type of food, or cooking location, from a drop down list. Data Validation Drop Down Doug Glancy sent me a sample data validation file that he created, that uses a variation on dependent data validation. I changed his file a bit, to "Thanksgiving" it up. The file has a list of Thanksgiving dinner guests, whether they'd like turkey, and if so, the number of slices. In column B there's a simple data validation drop down list, where you can select TRUE or FALSE. Conditional Data Validation In column C, there is a conditional data validation drop down list, based on
Continue reading Excel Conditional Data Validation
Many moons ago, Dave Peterson created a sample Excel data entry form and kindly shared it on the Contextures website. In Dave's original worksheet data entry form, users could add records on the data entry worksheet, and click a button to go to the database sheet, where they could review or edit the order records. I've created a couple of versions since then, including an Excel data entry form that allowed users to view, add and update records, while staying on the data entry sheet. Mark the Required Cells Here's the latest version of the workbook, with new features that were suggested in the comments for the previous version. The first new feature is the ability to mark which cells are required. In column E, add an X beside the cells that are mandatory, and leave the optional rows blank. Conditional formatting colours the required cells in green, and the
Continue reading New Improved Excel Data Entry Form
On the Consumerist website last week, they posted Lauren's Excel budget template, so I downloaded it, to take a look. I'd call it an Expense Tracker, rather than a "Budgeter", because it's used to record income and expenses. (Do you know the origin of the word "budget"? I had to look it up.) Here's what it looks like, with part of the formula for the Total cell showing in the formula bar. The grey fill colour is added with conditional formatting. Shown below is the full formula for the Total. You can see that Lauren has named the date headings (_8_10d) and hidden total row (_8_10) for each month. Wow! It makes me tired just looking at that. Lauren created a lot of named ranges, to set up the file, and she'll need to do more work to add more months. Because there's a separate
Continue reading Help Improve This Excel Expense Tracker
Are there files that Excel opens automatically, every time you start Excel? Something changed in your computer, and Excel files are opening automatically, and you want to get rid of them. Keep reading, to see where those files might be located, and how to stop them from opening. Also, remember to enter the Excel Giveaway at Simply Learning Excel, for a chance to win a 1-hour online Excel session (transferable) or an Excel book. Stop files from opening automatically in Excel 2007 and Excel 2010 To stop files that automatically open in Excel 2007 and Excel 2010, you can remove a folder name in the Excel options: Click the Office Button, then click Excel Options (In Excel 2010, click the File tab, then click Options) Click the Advanced category, and scroll down to the General section. In the box for 'At startup, open all files in', you might see
Continue reading Unwanted Files Open Automatically When Excel Starts
In January, you read about the Excel Weight Loss Tracker in which you could enter your current height and weight, and record your weekly weight loss. That version was in pounds. A couple of people asked about a stone/pound version, so I've finally created one -- just in time for swimsuit season!
Please consult your doctor about weight loss targets, and BMI measurements. I'm not a medical professional, and the workbook is designed for tracking your weight loss, as per your doctor's advice.
Update: There is a new version of the Excel Weight Loss Tracker in Stone, so the worksheets are slightly different from the screen shots below.
Enter Your Weight Loss Goals
The new stone/pound version works the same as the previous version, but you'll enter your start and target weights with stone weight in one column and pounds in another. The workbook then calculates the stone weight as
Continue reading Excel Weight Loss Tracker in Stone
|
|
Recent Comments