|
|
Last year, you might have seen the Excel Weight Loss Tracker workbook that I posted, which used inches and pounds as its measurements. Even though we use the metric system in Canada, we still track our weight in the Imperial system. (I'm not sure why.)
In May 2010, I posted the weight loss tracker in a Stone/Pound version, for the people who use that system.
Note: Please consult your doctor for recommended target weight and weekly weight loss goals. This workbook is designed as a recording tool only, based on the goals that you set with your doctor's advice.
[Update] The latest version of this workbook lets you select pounds or kilograms as the measurement. To download, see the link at the end of this article.
Metric Weight Tracker
If you'd prefer to track your weight using the metric system, you can download the latest version, which uses kilos. In
Continue reading Excel Weight Tracker Metric
With Excel VBA programming, you can add a Combo Box to the worksheet, to show a data validation list. Usually a single column combo box is enough, like this list of weekdays. In some cases, it's helpful to have a multi-column combo box. For example, instead of a single-column list with product codes, you could show the product names in another column. When you select an item from the drop down list, only the product code is added to the worksheet cell. The Lookup Lists On another worksheet, there are two named ranges – ToolList (A1:A7) and ToolListFull (A1:B7). The ToolListFull range is used as the ListFillRange for the Combo Box, and ToolList is used for the data validation list. Combo Box Settings To show two columns in the combo box, change its ColumnCount setting to 2. When you double-click on
Continue reading Multi-Column Excel Combo Box
With data validation and some programming, you can select multiple items from a drop down list, and show the selections in a single cell.
That technique is helpful in some situations, but it can make filtering difficult. With an AutoFilter turned on, only the combined results show in the filter options, not the individual items.
Filter for a Single Item
This week, Ben emailed to ask how to filter that combined list for a single item. For example, how could you see all the rows where Two was selected?
If you're using Excel 2010, you can type in the filter Search box, just above the list of filter options. As you type, the options are automatically filtered to show only the items that contain the text that you are typing.
In the screen shot below, I have typed "two", and only the matching options remain in the list.
Continue reading AutoFilter For Multiple Selections
In Excel 2007 and Excel 2010, when you create a new workbook, there should be 1,048,576 rows on the worksheet. However, one of my clients was creating new files in Excel 2007, and the sheets only had 65,536 rows, just as they did in older versions. Perhaps you don't need more rows than that, but if you've paid for a shiny new version, you'd like access to all of its features! Solve the Too Few Rows Problem At first, we thought the problem might be an old Excel 2003 template, that was starting automatically, and being used for the new workbooks. A search of all the Templates folders didn't turn up any suspects, so that theory was wrong. Finally, we discovered that the default format for saving files was set to Excel 97-2003 Workbook (*.xls). To get the full-sized Excel 2007 worksheets, go into the Excel Options,
Continue reading Too Few Rows in New Excel Workbook
With dependent data validation, you can make one drop down list depend on the selection in another cell. For example, select Vegetables as a category in column B, and you'll see a drop down list of vegetables in column C. Problems With INDIRECT This technique uses the INDIRECT function, to return the range with the name Vegetables. It works well with a static named range, but INDIRECT doesn't work with a dynamic range. For example, in the workbook shown below, there is a dynamic range named NumList, with the formula: =Admin!$A$1:INDEX(Admin!$A:$A,COUNTA(Admin!$A:$A)) You can SUM the dynamic named range: =SUM(NumList) for the correct result of 10. However, if you use the INDIRECT function, the result is a #REF! error. =SUM(INDIRECT(C4)) Dependent Data Validation Workaround Instead of using dynamic ranges with dependent data validation, you can use a named starting cell (Vegetables) and named column (VegetablesCol).
Continue reading Dynamic Dependent Excel Drop Downs
If you're building an Excel workbook, in which users with basic Excel skills will enter data, would you create a worksheet data entry form? Or, do you prefer to build an Excel UserForm? Worksheet Data Entry With the worksheet method, you can hide the data sheets, and protect the data entry sheets, so users can only enter data in the unlocked cells. You can add a few navigation and function buttons, to help users with basic Excel skills. An advantage is that you're using built-in Excel features, like data validation and formulas, so you can reduce the development time. Excel UserForm The UserForm method takes longer to develop, because you're adding another layer to the project. Advantages to this method include: combo boxes, which can be formatted, and have autocomplete (unlike data validation drop downs) tab order control, which isn't available on the worksheet, where pressing the
Continue reading Worksheet Data Entry or Excel UserForm
In Excel 2007 and Excel 2010, you can use icon sets in conditional formatting. For example, use Red, Yellow and Green stoplight icons, to highlight the good, average, and poor results in your sales data.
Rob emailed me recently, to ask how to limit the conditional formatting icons to 2 colours only, instead of the 3 or 4 default icon colours.
I am only interested in using 1 or 2 icons (a red X for "Off" and a Green light for "On" – not interested in the Yellow light). I want these icons to be triggered by a boolean (TRUE/FALSE) in another cell.
Create Your Own Icon Set in Excel 2010
Fortunately, if you're using Excel 2010, you aren't limited to the default icon sets – you can create your own, by mixing and matching from the available icons.
To create the icon set that Rob wants, I selected
Continue reading Customize Excel Conditional Formatting Icons
Data validation is one of the best features in Excel. You can use it to create drop down lists, or limit what users can enter in a cell. Unfortunately, data validation isn't perfect, or foolproof. Users can get around the limits, by pasting data into the cell, or by using the Clear All command in a data validation cell. Someone sent me a question this week, asking how to trap input errors, despite these data validation failings: Hello! I was wondering, to you have any idea how to error trap a date input? I will enter dates on a specific column (complete dates such as 11/05/2010) and error trap if the input has the year of 2011 and not 2010. I know data validation does that, but only if the cell is manually inputted. If the input in the cell is pasted, it does not do that anymore. Add
Continue reading Excel Data Validation Fails
If you're building a new city, or plotting world domination, you'll need a powerful project management tool, such as Microsoft Project. For smaller projects, you can list your tasks in Excel, and create a Gantt chart, to show the timeline. List the Project Tasks In this example, we're creating the company budget for next year. The first step is to list all the tasks on an Excel worksheet. The starting date for the first task is entered in column B, and the estimated days for each task is entered in column C. Then, in cell B3, the WORKDAY function is used to calculate the starting date for the second task. It calculates the date based on the start date of the previous task, the number of days for the previous task, and the days in the list of holidays, on a different worksheet. =WORKDAY(B2,C2,HolidayList) Create the Gantt Chart To
Continue reading Simple Project Planning With Excel Gantt Chart
With Excel data validation, you can create drop down lists on a worksheet. However, the font size is very small, and can't be adjusted, and you can only see 8 items at at time. With Excel VBA programming, you can add a ComboBox to the worksheet, to show the data validation list. In the ComboBox, you can control the font size and the number of visible items in the list. Problems in Excel 2010 Although this technique works nicely in Excel 2007, and earlier versions, you might have a problem with the ComboBox size in Excel 2010. In the screen shot below, the ComboBox is about 1/4" wide, instead of filling the entire cell. In other workbooks, the ComboBox is so narrow that you can't see it at all. That's not too helpful a feature! Fix the Problem in Excel 2010 Fortunately, the problem is
Continue reading Fix Combo Box Sizing in Excel 2010
|
|
Recent Comments