Multiple selection drop down?

I have made a drop down menu (data Validation) to select month and the value of same is displayed in the cell below cell which is updated in other excel sheet against particular month (using Index & match).

This is working fine.

I would like to select 4 or 5 months (multiple selections) from the drop down menu (data Validation) and display the values in cells below.

Is this possible?

2 Answers

I have made a drop down menu (data Validation) I would like to select multiple selections from the drop down menu

This is possible, though I needed VBA to accomplish this.

The code I am currently using is this:

 Private Sub Worksheet_Change(ByVal Target As Range) ' Vars to store the contents the cell which we are changing. Dim oldVal As String Dim newVal As String ' In range Dim rngDV As Range ' We do not even try to handle multiple cells at the same time. If Target.Count > 1 Then GoTo exitHandler ' We are called because a single cell is getting changed. ' If it is a drop down list then we want to act. If not skip dropdown code all the way to exit) On Error Resume Next Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation) On Error GoTo exitHandler ' rngDv is filled with Cells.SpecialCells(xlCellTypeAllValidation) ' That can be empty. In which case skip to end. If rngDV Is Nothing Then GoTo exitHandler ' Check if our cell is in range in a complicated way. ' If not insersect might be cleaner. Then I can remove the else. If Intersect(Target, rngDV) Is Nothing Then 'do nothing Else ' OK, we are changing one of the drop down lists in our range. ' Block new changes until we have finished. Application.EnableEvents = False ' Excel has already written the new value to the cell. ' This new value will replace our own content. ' Three options here: ' 1) We are adding a value for the first time. ' 2) We already have a value, opened drop down and did not select anything ' 3) We had a value and select a new option -> Action needed to APPEND rather than REPLACE ' Rescue the new value in a string newVal = Target.Value ' We also want the old value, bt atm it is already gone. Lets do a generic undo to get it back. Application.Undo oldVal = Target.Value ' Re-replace with new content. ' If we are in range for our special actions we do will do this it again later. ' If we are not in in our special section and do not replace then we effective ' block all changes. That is not desired. Target.Value = newVal ' Are we in range of [BC,BJ]? ' (A-Z = 1-26, AA-AZ is 27,52, ... ' If (Target.Column >= 55 And Target.Column 2) Then If (oldVal = "" Or newVal = "") Then 'Case 1 or case 2 as mentioned above Else ' Finally, actual action code! ' Returns an integer specifying the start position of the first occurrence of one string within another. ' ' ' We are going to use this for a mode 1 (text) comparision between the old cell content and the new entry. ' If the new entry is already somewhere in the old string then instr returns its position. ' ' Creatively use this to check if the value is present (we do not care WHERE in the string). ' If (InStr(1, oldVal, newVal)) > 0 Then ' If we already have the same aswer in our cell. ' Instead of appending it and thus adding it a second time we will remove it. ' This allows use the easily remove a selection from a list, even if we already have a long list of entries. ' ' Right returns a string which we ca compare with. If Right(oldVal, Len(newVal)) = newVal Then Target.Value = Left(oldVal, Len(oldVal) - Len(newVal) - 2) Else ' Replace ( string1, find, replacement, [start, [count, [compare]]] ) ' ' Search in the string with the old answers. ' for "new value, " ' and remove it by replacing it with nothing. ' Target.Value = Replace(oldVal, newVal & ", ", "") End If Else ' Append the new change. Add a comma as cvs like separator. Target.Value = oldVal & ", " & newVal End If End If End If ' end of If Intersect(Target, rngDV) End If exitHandler: Application.EnableEvents = True End Sub 

It is my first attempt at VBA and some values are rather hardcoded.
(E.g. If (Target.Column >= 55 And Target.Column <= 62 And Target.Row > 2) to select the range I am acting on).

This effective concattenates all the selections in one cell, comma seperated. If you want to act on that data you will either need to tokenise it again, or use if statements to cover all possible combinations.

and display the values in cells below.

You can certainly copy a cell. The current code combines all selections (and removes one if you select it twice) and puts that as a string in a cell. That string can be copied to any location using standard excel functionality.

If you mean Drop Down List based on a selected month you have to write your months then have a header column with each month and down the values corresponding then define name of this column like the name of the month and with data validation choose List in Settings and =Indirect(reference of the month) January February March then in another column January value1 value2 value3...select and define name as January in column February info1 info2 info3... select and define name February... in the sheet where you choose the month (B2) with data validation list for months in the other cell make data validation settings list and source = Indirect(B2)