will introduce you to the exciting new pivot table features in Excel 2007. Create quick summaries and pivot charts, add impact with traffic light icons, design calculated fields, group dates and numbers.
Select Multiple Items from Excel Data Validation List
You’ve probably used an Excel data validation drop down list, where you can click the arrow, and select an item to fill the cell. In the worksheet shown below, you can select a month from the drop down, and it overwrites any previous selection in that cell.
Select Multiple Items From the Drop Down List
Instead of limiting users to a single selection, you can use a bit of programming, combined with the data validation list, and allow multiple selections. You can display all the selected items across a row, or down a column, or keep them in the same cell.
Fill Across a Row
For example, each item selected in this drop down list fills the next empty cell at the right. You could use this technique to fill player names across a list of innings, or employee names for days of the week.
Fill Down a Column
In some worksheets, you might prefer to fill down, instead of across.
Add Values To the Same Cell
Instead of filling other cells, you can keep all the selections in the same cell, separated by commas.
Use Programming to Add Multiple Values
The code to allow multiple selections runs automatically when you make a change on the worksheet. To see the code for the SameCell sheet, you can right-click the sheet tab, and click on View Code.
In the code, column 3 is the only one where the change will occur. In your workbook, you could change the 3 to a different number. Or, if you don’t want to limit the code to a specific column, you could delete the two rows that are marked with a red circle.
Download the Sample Data Validation File
To experiment with this technique, you can download the zipped sample file: Select Multiple Items from Excel Data Validation List
Watch the Data Validation Video
To see these techniques, and a few other multiple selection examples, you can watch this short video.
_________________
September 18th, 2009 at 1:56 am
Excellent. This is just what I needed. I actually extended this a bit so that the default entry on the list always says "pick from list".
Next thing to add would be a feature that goes through the already created list of values from the drop-down list and exclude those values on the drop-down that have already been added.
September 18th, 2009 at 8:26 am
Thanks ingeno79, adding a default entry is a good idea.
To exclude the items that are already added, you could combine this with the technique in this tutorial:
Hide Previously Used Items in Dropdown
September 23rd, 2009 at 4:53 am
This works great, but I have moved the script into a new workbook and each time I open this new workbook and try to make a change in the dropdown list a warning comes up to say that the cell or chart is protected. I can unprotect the sheet by going to tools and protection, but each time I open it i get the same error message. I need to remove this automatic protection which seems to come into effect on open so that I can have other users use my sheet without getting warnings.
Your help would be much appreciated.
September 24th, 2009 at 2:10 pm
SP, there's code in the ThisWorkbook module, that runs when the workbook opens. Perhaps you copied that to your new workbook too. If so, just delete that, and the SameCell sheet won't be protected automatically.
September 28th, 2009 at 9:33 am
Thanks Debra - works great now.
October 4th, 2009 at 10:00 am
Debra,
Great work on the multiple item validation. I was wondering if there was anyway that I could change the macro for "LineBreak" such that I could limit it to a single or multiple columns but not the entire spreadsheet.
Thanks,
Steve
October 5th, 2009 at 5:11 am
Is there any way that I can have more than one cell use this code instead of just column 3 as in the example?
Thanks,
Kai
October 9th, 2009 at 10:47 am
marvelous, didn´t know it was possible.
it is exactly what I was in need.
thx,
Rafael
March 10th, 2010 at 3:45 pm
Works great, but..... One question. If you select an item in err, what is the best way to remove it? I went to the formula bar and deleted the duplicate item but it gives me an err " The Value you enter is not Valid" "A user has restricted values that can be entered into this cell"
Also, when I select more than two items I receive the err " The value in this cell is invalid or missing, ...."
Thanks for your help,
Juanita
March 17th, 2010 at 10:55 am
Juanita, if you want users to be able to edit the cell, you can change one of the Data Validation settings. On the Error Alert tab, in the Data Validation dialog box, remove the check mark for 'Show error alert after invalid data is entered'
March 22nd, 2010 at 12:56 pm
I am trying to copy and paste the code for the same cell with comma separators to work for column T (which is column 20?) And it isnt working for me.
I even tried applying to the entire worksheet and deleting the lines with the red dots, but that isnt working either.
Any tips?
Thanks,
Cameron
March 26th, 2010 at 6:13 am
I am a little puzzled as to how to use filters along with the multi select drop down lists to search or isolate particular values. My programming skills are very limited so I am having some difficulty working this out. Other users of my spreadsheets have very little knowledge of how it works so I need it to be as easy and foolproof as possible. Would it be easiest to used the column option or the single cell, and will the filter like this?
March 26th, 2010 at 11:50 am
Hi Mary,
If you need to sort, you will need to separate out your items. It will sort by the first item it see in the cell.
Hope this helps,
Juanita
March 29th, 2010 at 12:20 pm
I am really a novice programmer and I can't seem to get this to work for me. Can you please explain how to modify the code to apply the comma separators in the same cell to the entire sheet?
Any help is appreciated!
Thanks!
Cameron
March 29th, 2010 at 4:38 pm
Cameron, right-click on the CommaSeparated worksheet tab and delete the lines with the red dots. Then the code should apply to the entire sheet.
April 1st, 2010 at 10:17 am
I am trying to use the code from the "Same Cell" tab. I am deleting the lines with the red dots and I can not get the code to run.
I am obviously doing something wrong, but I do not know what it is.
I am inserting a module in VB, pasting the code, deleting the lines with red dots (without re-formatting the code. Do I need to be deleting the blank lines where the code used to be and evening up the tabbing of the "End If"s) and then hitting run, naming it "comma" and trying it out in the sheet.
Can you point out if this is the wrong way to go about this?
Thanks!
Cameron
April 1st, 2010 at 10:53 am
Cameron, the code should be pasted onto the module for the worksheet where you want to use the code.
So, if you're trying to use this on a worksheet named "MyDataEntry", right-click on that sheet's tab, click View Code, and paste the code there.
April 1st, 2010 at 11:03 am
hallelujiah!!!
it works!
thanks you, debra, so much!
April 1st, 2010 at 11:44 am
Cameron, you're welcome! Glad you got it working.
April 6th, 2010 at 6:28 pm
I have downloaded the zipped file but when I go to try the different tabs they are not working. When I copied the SameCell coding to my spreadsheet it worked great but I wanted to try some of the other methods and now none of them work. Help!
April 6th, 2010 at 9:10 pm
John, make sure to enable the macros when you open the sample file, and then the different tabs should work.
April 7th, 2010 at 11:57 am
Thanks for your response. I must have glitched something because after I closed all my open Excel files and re-opened Excel and everything worked fine.
I need your help on some of the programming. I want to limit the programming to a specific column because in other columns I want to use a regular pull down menu. This is the first time I have ever enter any type of programming in Excel! What additional programming language would I need to use to limit the CommaSeparated, LineBreak and SameCell programming to a specific column (Column 10 in my spreadsheet)?
Thanks!
April 7th, 2010 at 7:24 pm
John, most of the examples refer to a column number, e.g.
If Target.Column = 3 Then
Change that column number to 10, and it should work the way you want.
April 30th, 2010 at 4:25 am
Hi Debra, Your script on this multiple selection on drop down list is very useful. For the "SameCell" sample, one issue I encountered is when you selected a wrong entry and tried to delete it, entry can not be deleted but instead the cell will contain "Old value" + "new value".
Sample:
Old Value = "One, Three" {then I want to delete "Three" by selecting "Three" and press Delete , then press Enter}
New Value = One, Three, One (which is the Old + New)
Hope you can help me with this bug. Thank you.
April 30th, 2010 at 7:18 pm
Grace, unfortunately, there's no way to distinguish your correction from a selection in the drop down list. When you change the cell contents to "One", that becomes the new value, and it's added to the old value.
You could clear the cell, then select One from the drop down list.
May 7th, 2010 at 4:43 pm
Hi, I am able to apply this code to my worksheet just fine. However, when I select an item in error and I try to delete it from the cell, it actually adds another "wrong" item. The only thing I can think of is to clear the entire cell and start over with my selections. Is there any other way to work around this?
Thanks in advance
May 8th, 2010 at 10:58 pm
Hi Michelle, you can clear the cell and start over, or stop the event code from running while you make the change.
May 31st, 2010 at 11:02 pm
Dear Debra,
The examples are just great! Esp for those who don't have programming skills like me (completely a layperson).
Currently am following the example - LineBreak. In my case, most of the pre-defined items are quire long (the longest has 249 characters so at least I have to set "110" width) in order to get a full display view from the drop-down box. I was wondering if there was a way to move the "selected items" cell down to next row instead of new column. i.e. Pick from C3 and the selected options to be displayed on C4?
Thanks
June 2nd, 2010 at 5:03 pm
Hi Debra,
This is almost exactly what I have been attempting to accomplish. However, I need this to run on multiple columns, but not the entire workbook. Is there a simple way to do this?
For example; I want it to apply to all rows greater than 2 and all columns higher than C...
Thanks for the help!
June 3rd, 2010 at 7:04 pm
weikee, you can change the code for the line break example, so the text goes into the cell below:
If Target.Value = "" Then GoTo exitHandler If Target.Offset(1, 0).Value = "" Then Target.Offset(1, 0).Value = Target.Value Else Target.Offset(1, 0).Value = _ Target.Offset(1, 0).Value _ & Chr(10) & Target.Value End IfJune 3rd, 2010 at 7:07 pm
Michael, you can change the code so it checks the target's row and column:
June 3rd, 2010 at 9:42 pm
Hi Debra, It works perfect! Thank you so much again for sharing your expertise.
weikee
June 8th, 2010 at 2:06 am
How can I make this work for Excel 2007?
June 8th, 2010 at 5:45 am
Hi, this is fantastic. I am very novice and have a small problem. I used adding to the same cell macro and changed column to column 6. I also want to run the same macro but from column 8 in the same spreadsheet, but I get an error message. i have tried simply calling it a different name but obviously i am doing something wrong. I would appreciate any help you can give me. Keep up the good work, this is fab. Thanks, Anna
June 8th, 2010 at 11:56 pm
boeh, the macro should work in Excel 2007. What problem are you having? Did you enable macros?
Tinks57, in the code, you can change the line that mentions the column, to include both columns. For example,
June 9th, 2010 at 9:19 pm
These tools are really great. I cna see many great uses for these. How would I modify the code to get a combination of samecell + line break. I'm looking to have multiple selections show up in the selection cell and seperated by a line break (not the cell to the right of the selection cell)
Thanks.