By clicking the tabs of sheets at the bottom of the Excel window, you can quickly select one or more other sheets. To enter or edit data on several worksheets at the same time, you can group worksheets by selecting multiple sheets. Sep 24, 2014 - How do I view multiple Excel workbook sheets side by side? To be able to view both (or more) at the same time, you will need to create a new window. Mac compatible versions of Excel should have similar options.
One of my colleagues asked me if it is possible to make multiple selections in a. When you create a drop-down list, you can only make one selection. If you select another item, the first one is replaced with the new selection. He wanted to make multiple selections from the same drop down in such a way that the selections get added to the already present value in the cell.
Something as shown below in the pic: There is no way you can do this with Excel in-built features. The only way is to use a VBA code, which runs whenever you make a selection and adds the selected value to the existing value. This has been one of the most popular Excel tutorials on this site. Since I get a lot similar questions, I have decided to create an FAQ section at the end of this tutorial. How to make Multiple Selections in a Drop Down List In this tutorial, I will show you how to make multiple selections in an Excel drop down list (with repetition and without repetition). There are two parts to creating a drop-down list that allows multiple selections:.
Creating the drop-down list. Adding the VBA code to the back-end. Creating the Drop Down List in Excel Here are the steps to create a drop-down list in Excel:. Select the cell or range of cells where you want the drop-down list to appear (C2 in this example). Go to Data – Data Tools – Data Validation. In the Data Validation dialogue box, within the settings tab, select ‘List’ as Validation Criteria.
In Source field, select the cells which have the items that you want in the drop down. Now, cell C2 has a drop-down list which shows the items names in A2:A6. As of now, we have a drop-down list where you can select one item at a time (as shown below). To enable this drop-down to allow us to make multiple selections, we need to add the VBA code in the back end. The next two sections of this tutorial will give you the VBA code to allow multiple selections in the drop-down list (with and without repetition). Thx Hyperion135: had to make a few changes to your code.
Here is what your final code looks like inserted in the original “without repetition code” provided by Sumit Bansal. Works like a charm! Thanks for the tutorial but I think I must have missed something.
I put information for drop down list in Sheet 2. Sheet 1 – created drop down list and targetted information in Sheet 2.
Pressed Alt-F11 and got the Visual Basic for Applications (VBA) screen, selected Sheet 1, pasted the code and closed VBA. Tried to select more than one item from drop down list and only got one item showing at a time. Reopened VBA, deleted code from Sheet 1 and copied the code to Sheet 2 then closed VBA. Tried to select more than one item from drop down list and only got one item showing at a time.
Not sure if need to have a particular cell or sheet selected before Alt F11? Using Microsoft Office 365 ProPlus.
I have just made my excel worksheet Marco enabled and added the code to the code window saved and closed. If I have to use the code for two columns specifically, say column D and column I which have different sets of values in the respective drop down lists, how will the code change? This is a little bit late, but I just came across this and have a couple questions.
Is there a way to limit the amount of selections one can make in a drop down list to say, 5? Currently there’s roughly 200 items in the list and I only want them to be able to select a maximum of 5 of those at a time. Also, say I have the same list in all of column 7 and 8, would I be able to have a command that tells the user that once something is selected in column 7 they can no longer select it in column 8 or vice versa? Hi Sumit – Thank you for this great information! I have an additional question We have a worksheet with many picklists, and I am using this code for all multi-select picklists. We do allow the user to enter a new value if needed. Then we use “circle invalid data” to find the new values that have been added.
However, when using “circle invalid data” on the multi-select picklists, it ALWAYS circles the cell if there is more than one value entered, even if they are valid choices. Is there a way to get “circle invalid data” to work properly with the multi-select columns? If not, is there something we could add to the code, to “highlight” those values that the user added (that are not valid)? Thanks so much for your help! I’m curious on how one can create dynamic multiple items list. For example let us say we have the following: Column 1 Drop Down Colors: Blue, Yellow Column 2 Drop Down Blue: Light Blue, Medium Blue, Dark Blue Yellow: Light Yellow, Medium Yellow, Dark Yellow Column 2 is dependent to Column 1. Thus, if I pick “Blue” in column 1 then in column 2 I have the choice to pick Light Blue and/or Medium Blue and/or Dark Blue If I picked “Blue” and “Yellow” in column 1 then in column 2 my options to pick are: Light Blue and/or Medium Blue and/or Dark Blue and/or Light Yellow and/or Medium Yellow and/or Dark Yellow Thank you.
Hi, Im using the multiple selection dropdown list. I don’t know how to describe but hopefully with my example below will make you to understand:- In my dropdown list have a several option where each option have their own values. Grape – 4 apple – 3 banana – 2 orange – 1 when i choose in the dropdown for Grape,Banana,Apple the excel will look for the lowest value among the option i had choose. In this case, the excel will find that Banana have the lowest value among the option i had choose hence the value showed up is 2. How can do that? Dear sir, Can you help me up how to loop range till 2 to 5000 for below code Option Explicit Private Sub WorksheetChange(ByVal Target As Range) If Target.Count 1 Then Exit Sub Application.EnableEvents = False If Target.Address = “$C$2” Or Target.Address = “$D$2” Then If Target.Address = “$C$2” Then Range(“E2”) = Range(“E2”) + Target ElseIf Target.Address = “$D$2” Then Range(“E2”) = Range(“E2”) – Target End If Target = “” End If Application.EnableEvents = True End Sub Sub Evenement Application.EnableEvents = True End Sub.
I’m having a difficult time implementing this solution for my particular use case. In my spreadsheet, I am applying data validation on the fly first — in other words, every time I click on a cell in a given range on my sheet “User Lists” it checks the header of that column, looks for that value in the header row on “User Picklists” and then if it finds it it uses the list from that page as the list for data validation on User Lists.
Some of the columns need to be Multi-Select though, so once that code block runs, I have used yours immediately below it. However, it’s not working the way I expect it to even though I left the code almost identical to how you are using it above. The difference is in your sheet, the code fires when I select a value from the list. In my sheet, it fires as soon as I click the cell and doesn’t re-fire when I select the value. I believe this has to do with the other code block above it, but I’m not sure how to make your block re-fire when I select the value.
Do you have any tips? Hello, Both of the codes worked great! However I am trying to combine both of the codes in the same file (workbook).
One column I need to select multiple item with repetition and the other I need to select multiple items without repetition. How do I combine these? I tried to use your code and I am getting my list from sheet 2 and I can get the drop down to allow me to pick one name but I am unable to to pick more than one. I assume it has to do with the way I am tying is into the sheet 2. Should I be using the Target.Address = “$F$6” or Sheet2!A1:A12 where my list is. Hello, using a VBA code similar to yours, I am selecting multiple items in a drop down list that are separated by comma. The code I am using is to edit and add multiple items in a drop down in the same cell.
I am trying to create a pivot table with independent filters instead of all the line items in each cell. For example, in my drop down list, in one cell, I selected apple orange and banana, and in another cell, I have kiwi orange and banana, however, I just want to focus on the banana independently that occurred each time. Is this possible at all? Or will I have to resort to traditional excel and create a cell for every single item. (Really what I am doing is monitoring donor/patient reactions, so I am selecting for example, nausea, loss of consciousness, etc, and where the conditions were mild, moderate, and severe and how many times in a mild reaction did a donor have nausea symptoms or something).
Hope there’s something to do this! I just like that the drop down feature makes one column instead of a million different columns but I really need it to analyze my data. I used the code above to allow for multiple selections, and then I used the modification from Sumit Bansal to modify it work for all of the drop downs in my worksheet. This worked great! Now my problem is that in the header row of my table I can’t make any changes. Any time I try to make changes the text keeps multiplying instead of deleting.
I am thinking that this might have to do with the fact that I had converted this spreadsheet to a table before I add the dropdowns and code. So the header row had it’s own built in sorting/filtering dropdowns that the code may be messing with?? Regardless it is huge table and now I am not sure what to do with it, and would appreciate any suggestions. My header row is mess and everything I try to do to fix it is making it worse. I used your code for being able to do multiple selections from a drop down menu, and then I modified it to apply to all the drop downs in my worksheet as described below. This worked great. Now I am having this weird problem where if I try to delete and edit something in a cell that does NOT have a drop down it won’t allow me to delete.
So every time I try to delete and add, I just end up with more and more copies of almost the same thing. The only thing I can think of is that it is somehow related to the code allowing for multiple selections. Have you ever seen this?
Do you know what it might be? Hi, thanks for the great code. I have it working on 4 separate columns in a file I have, but there is a strange issue I’m seeing.
If I enter data in a cell directly in front of one of the columns I have the VBA code running against and then either tab into or right arrow into the coded column, the cell highlight will jump back to the cell I came from. It then also runs the VBA code against that cell now too. Example: column B has the VBA code applied to it, column A does not. If I enter any data into column A, press TAB to go to the next cell in the row which is in column B. The cell highlight moves to column B, briefly, then jumps back to column A. If I had for example entered “1” in the cell in column A prior to pressing TAB, if I then enter “2” in that same cell, the VBA code will make the data in the column A cell be: “1, 2”.
Any clue on what I can change in the code to keep this from happening? Here’s my version of the code posted here, which is based on the code posted for keeping duplication of choices from happening. It looks for column headers by title to determine which columns to run the script on.
Hello, this is super helpful, thanks! Only thing is I can only get this to work when I use it alone without any other VBA code, but as soon as I use additional code to have my sheet perform other functions, my drop-down list reverts to only accepting one option at a time. Below is the code – any ideas how I can get it all to work? My other two commands are for time stamps in two different places on the same sheet.
The other two commands still work when I combine all the code, but the ability to choose more than one option from the drop-down menu stops working. This was helpful, but is there a way to have this AND have code so that the duplication in the cells stopped?
I was using Spyrule’s code he posted above, which is: If Oldvalue = “” Then Target.Value = Newvalue ElseIf Target.Value = Oldvalue Then ‘. Hi Sumit, Great code here. The problem is (keep in mind it may be on my end), the code is not working. I even downloaded your code, and tried running it on my machine, and I still am only able to select one of the drop list items.
Is it possible there is a security setting I missed? I have enabled macro’s, and allowed trust access to VBA project object model, but again, the code doesn’t work. Also, is it possible, to have the code work on several different cells (copy and paste drop down) and have the VBA code changed to accommodate? I am trying to make a drop down selection list down over 260 line selections. I essentially need to copy the drop down to that many, and allow for selection as I go through the line items. Thanks again. Hi Sumit, thanks for the codes.
The multi-select list worked great but I have issue with the existing single-select list. Before I added your code, validation for the single-select list worked. I only could select the value from the list.
But after I added your codes, the validation for single-select list didn’t work. I was able to enter any values to the single-select list and I didn’t get an error message. If I only select the value from the list then it’s fine. But I still could enter any value and the cell will accept it. Do you know how to fix this? Thanks so much for your help!
I think I’m having the problem of duplicating values. If I make multiple selections from a drop down list for a cell and then try to put in something that is not from the drop down list, I end up with several repetitions of what I had selected in the same cell. Then when I try to delete some of the repeated values, it will repeat itself again in the same cell. So it becomes an endless cycle of repetitions until I just delete the entire cell. But I can’t figure out how to fix this. I tried to put in the code that spyrule shared but I may not be doing it right. I’m new to Vbasic so I don’t really understand the code This is what I ended up with: Oldvalue = Target.Value If Oldvalue = “” Then Target.Value = Newvalue Else If Target.Value = Oldvalue Then GoTo Exitsub Else Target.Value = Oldvalue & “;” & Newvalue End If End If End If Application.EnableEvents = True Thank you for posting.
This was really helpful. This is great! Thank you so much for this. I have one question though. I’ve read the comments and can’t find the same question being asked – apologize if I missed it. I am able to select multiple options from a dropdown box successfully, however I can’t find a way to them remove one of the options unless I clear the entire cell.
For example, I select options such that my cell looks like: Apple, Orange, Banana. I no longer want Orange selected. If i try to delete the Orange text, it doesn’t work.
The only way I’ve found to do this is to delete all contents of the cell, then go back and select Apple and Banana from the dropdown. Hi there – I tried to use this code but it is not allowing me to add multiple values in 3 of my columns. Hi Sumit, This worked great thanks, this question leads on from what I have now achieved with this code. Now that I have selected multiple entries in some cell (I have applied this code to an entire column) but not all, I would like to filter down to entries within that column, ie find all entries that contain orange or blue. So I have applied the usual filter to my heading row but when I click on this I would like the options to filter to appear the same way it would if there were only one entry in each cell, but it has the lists/multiple entries as options. Basically I want filter function to comma separate my lists I guess?
Does that make sense? Any suggestions? I can just type in the colour in the search option under filter but that doesn’t work if I want multiple colors at once. This is really great, I’ve been looking for this option.
My question is, I’ve followed your instructions from above to modify the code and get it working on just one column, in this case column 7. However, I’d like to get it working on column 6 too but instead of having the comma seperate each value I want to use a hyphen instead. So currently on column 7 the output is “1, 2, 3, 4”.
On column 6 I want the output to be “1-2-3-4”. I’ve played around with the code a bit but I can’t seem to get it rightany suggestions?
Hi Sumit, I have run into a snag. I started with your code at the top of the thread and started making changes based on your recommendations. Allow for multiple selections. (First code given on page) -Works. Changed the code from Target.address = “$C$2 Then to If Target.Column = 10 And Target.Row 3 And Target.Row.
Below is the code you can use. If there is already a value in the cell and you select it again, it will not append it to the existing value. Never mindfigured it out! Thanks for the post/information though! ‘ I just swapped out Target.Address for Target.Column.
Tested and working. Sumit – I was wonder if you could assist me.
I have a column titled ‘Services’ and have created dropdown list in each cell. The worksheet has about 186 rows (and growing). I used this code to be able to select multiple and have them show up in each cell.
I took out ‘IF Target.Address = “$C$2” Then and one END IF and not it works for all cells that has the dropdown list. HOWEVER, whenever I type anything in any other cell, it doubles/replicates what I already had in there plus what I was typing.
I’m guessing that is because of the code I put in for the dropdowns. Could you help?
How To Save Multiple Sheets In Excel To Pdf - how to save multiple sheets in excel a pdf mac fix or export each sheet as csv file selection entire workbook send spreadsheets pdfs accountingweb2 easy ways convert wikihowexcel 2013 saving and sharing workbooks page 3converting an with worksheets inidual vba create from email it outlookconverting charts files stack overflowexcel 2 wikihowhow active one all youtubeexcel exportasfixedformat document not saved by means of microsoft online convertersexcel step guide 10 examplesconvert converters. 2490 x 1497 =. 9247 x 3700 =.
9643 x 8594 =. 5407 x 8635 =. 4658 x 9958 =. 3526 x 8953 =. 5501 x 8023 =.
9740 x 2269 =. 3702 x 2268 =. 8857 x 5313 =. 9932 x 3496 =. 7581 x 9316 =. 8850 x 1115 =.
7944 x 5461 =. 4624 x 4225 =. 2819 x 9766 =. 2378 x 7672 =.
6687 x 1403 =. 8160 x 5583 =. 2254 x 2871 =. 6619 x 7856 =. 6033 x 8969 =. 7107 x 4161 =. 3505 x 1252 =.
5598 x 6676 =. 6450 x 6156 =. 9995 x 5289 =.
2404 x 2041 =. 5143 x 5913 =. 7371 x 8896 =. 4924 x 6821 =. 9818 x 7127 =. 9753 x 7131 =.
1794 x 4341 =. 9213 x 6355 =. 6768 x 5335 =. 5789 x 2147 =.
1228 x 9117 =. 9578 x 2109 =. 3852 x 1387 =. 8725 x 1284 =.
2851 x 7908 =. 5231 x 1621 =. 5609 x 8336 =. 8128 x 3789 =. 4718 x 4942 =. 3677 x 3975 =. 7369 x 8891 =.
5895 x 5067 =. 8586 x 7569 =. 2528 x 3865 =. 5077 x 5483 =. 4205 x 9927 =. 7854 x 4129 =. 9429 x 6089 =.
9108 x 9200 =. 4879 x 5613 =. 8497 x 9995 =. 9393 x 3739 =.
6505 x 4586 =. 8144 x 3418 =. 3010 x 5335 =. 9186 x 3551 =. 4608 x 7815 =.
5579 x 7566 =. 1319 x 7571 =. 5691 x 7296 =. 7284 x 8626 =. 9722 x 3027 =.
6570 x 3258 =. 4339 x 8090 =. 7456 x 9405 =.
7824 x 1662 =. 1238 x 8154 =. 2952 x 8477 =. 3355 x 2667 =. 5093 x 4238 =. 7557 x 7004 =.
3261 x 2260 =. 8443 x 9621 =. 9035 x 5087 =. 8200 x 6843 =. 7348 x 8648 =. 1286 x 8083 =.
2976 x 9738 =. 3993 x 8786 =. 5675 x 1195 =.
9714 x 5114 =. 7552 x 5226 =.
3220 x 4222 =. 8553 x 3808 =. 7172 x 8993 =. 5549 x 9938 =.
5382 x 3621 =. 4597 x 4171 =. 9821 x 3008 =.
3081 x 5629 =. 6868 x 8549 =.
3636 x 8315 =. 6327 x 9027 =. 9671 x 5676 =. 3279 x 6147 =. 1330 x 1571 =.
2588 x 4305 =. 6044 x 6037 =.
1151 x 6247 =. 4041 x 2306 =. 3366 x 1800 =. 1014 x 6274 =. 3373 x 7309 =. 9261 x 5552 =.
1463 x 3292 =. 3142 x 6286 =. 3720 x 9990 =. 1939 x 3969 =.
7553 x 9834 =. 6914 x 6340 =. 4739 x 3919 =.
8215 x 2381 =. 4097 x 3580 =. 1825 x 4110 =.
3616 x 6510 =. 3566 x 3413 =. 5468 x 9375 =. 2470 x 8149 =. 8950 x 3637 =. 4139 x 6247 =. 7627 x 8841 =.
4275 x 4906 =. 1391 x 5754 =. 5347 x 2592 =. 3016 x 6116 =. 7539 x 1391 =.
2982 x 7440 =. 3960 x 8899 =. 1678 x 3607 =. 1881 x 1621 =. 8226 x 9105 =.
3967 x 9365 =. 2654 x 2025 =. 9152 x 7914 =.
4432 x 6708 =. 4272 x 8258 =.
4563 x 7298 =. 7707 x 9481 =. 3875 x 8768 =. 5283 x 7728 =. 8678 x 9165 =. 3329 x 6978 =. 4971 x 7640 =.
9757 x 4242 =. 1904 x 3609 =. 1784 x 9331 =. 6568 x 4045 =. 4169 x 7497 =. 2096 x 2575 =. 8457 x 8362 =.
7414 x 4103 =. 7262 x 3645 =. 7271 x 2603 =. 1970 x 4347 =. 4369 x 5033 =. 4678 x 3945 =. 3553 x 6072 =.
8296 x 5534 =. 3023 x 7772 =. 8565 x 7030 =. 3294 x 3502 =.
3509 x 4489 =. 9031 x 6113 =.
8829 x 5516 =. 6211 x 3295 =. 6646 x 3615 =. 7674 x 4579 =. 3832 x 9978 =. 3493 x 8383 =. 6011 x 3981 =.
1318 x 9527 =. 7995 x 7970 =.
9961 x 6877 =. 9319 x 4655 =. 6242 x 6847 =. 6289 x 8327 =. 7439 x 8356 =. 6448 x 2220 =. 8023 x 1676 =.
3958 x 1382 =. 7652 x 2696 =. 8362 x 4215 =. 5466 x 9920 =. 9175 x 2250 =.
6118 x 9760 =. 7981 x 5455 =. 1331 x 7526 =. 2049 x 6259 =.
4989 x 6940 =. 5360 x 2266 =. 5388 x 1123 =. 7519 x 8553 =.