|
|
I've done another update to the Data Validation Multiple Selection sample, thanks to a question from Pat, in a blog comment. He has 3 columns with different drop down lists, and wants to add new items to the applicable lists. I can get 3 separate columns to each allow multiple selects from their own lists and to allow write in contents...But what I want ...is to make the selected columns (not all) so that write in selections are written to the lists and the lists are sorted. So, in the new example, on the SameCellAddSort worksheet in the sample workbook, that's what happens. You can do a multiple select in columns C and D, but only a single selection in column B. Add New Items New items can be added to the Names and Numbers columns. If a new name is entered in column C, is will be
Continue reading Excel Drop Down Multi-Select Update
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
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
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
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
With a bit of Excel VBA programming, you can change an Excel data validation drop down list, so it allows multiple selections. There is a sample file and instructions, in a blog post that I wrote a couple of years ago – Select Multiple Items from Excel Data Validation List.
There is a video in that post, that shows how the multiple selection options work, and a peek at the code that makes the multiple selections possible.
Setting up the Workbook
Even though the post is a couple of years old, it still gets comments and questions. This week, Rick asked for details on setting up a new workbook, and copying the sample code into the new file.
To help, I've recorded another video, showing the setup steps.
More Articles on Select Multiple Items
[Update]: Since posting this article on selecting multiple items from an Excel drop down list, I've
Continue reading How to Set up Multiple Selection Excel Drop Down
There's a Golf Tee Time Excel workbook on the Contextures site, that I've updated, to add a few new features. You'll list all the players, then assign groups of players to a selected tee time. The new version has data validation in column C, which limits the selection to 4 names, and you can't mark a name that is already booked. The time selector only shows tee times that haven't been assigned. Assign Golf Tee Times On the MemberList sheet, you can enter a list of names in column B. Mark up to 4 names, by typing an X in column C. Then, select a start time for the marked names. The drop down list only shows the available times. In the example below, the 8:20 AM time has been filled, so it's not in the drop down list. Finally, click the Book Times button, to
Continue reading Set Up Golf Tee Times in Excel
A couple of years ago, I described how you could select multiple items from an Excel drop down list. One of my clients needed that feature in a workbook last week, so I've made an enhancement to the VBA code.
Problems With Editing
In the previous version, you could select multiple items, as promised, with no problems. However, if you tried to edit the cell, you might have seen the data validation error alert.
To prevent that error alert from appearing, you can turn off the setting.
However, the helpful VBA code simply added your revised text, to the existing text -- probably not the result that you wanted!
Add an Editing Check Box
In the new version, there is a check box on the worksheet. If you add a check mark, you can edit the cell, without any problems.
To add the check box:
On
Continue reading Edit Multiple Selections in Excel Drop Down Lists
Yes, it's Valentine's Day today, and if you were too busy to buy your sweetie a card yesterday, you can make one in Excel. Phew!
Your boss won't mind if you spend a couple of hours working on this today, because it's an Excel project! This Excel Valentine card uses a named range, data validation, a formula, and conditional formatting (to change the heart from white to pink to red).
If you won't have time, or if your drawing skills are worse than mine, you can download the sample Excel Valentine file, at the end of this blog post.
And if you want some romantic music in the background, while you work on your Excel Valentine card, you can listen to the YouTube playlist, compiled by John Walkenbach and his blog readers.
Set Up the Worksheet
To create the heart shape,
Start by making columns A:M narrower, to create square
Continue reading Make a Valentine Card in Excel
|
|
Recent Comments