Excel – Multi-value selection in an Excel cell

data validationmicrosoft excel

There's a way to make an Excel cell kind of a dropdown selection box. You would just go to Data->Validation with a cell or a range of cell selected, select list as the data type and specify the list of values.

I would like the dropdown be a multi value select, kind of a multi value lookup.
I suppose there's no built in support for that, but there may be some third party solutions utilizing the macro capabilities. Googling gave me an example of such a macro but it is far from the experience of a real multi value lookup when you just mark the selected items with check boxes to the left or select the values in a kind of a popup window. (You would select the items needed from a single-value dopdown ny sequentially selecting each item and the macro would populate an adjacent range of cells with the selected values, putting every value in a new cell).

What's is the most native looking way to make a multi-selection cell in Excel?

Best Answer

See the Forms section in Excel Help for the topic "Add a list box or combo box to a worksheet".

From that page (referring to a Form control list box):

Note: If you set the selection type to Multi or Extend, the cell that is specified in the Cell link box returns a value of 0 and is ignored. The Multi and Extend selection types require the use of Microsoft Visual Basic for Applications (VBA) code. In these cases, consider using the ActiveX list box control.

And referring to an ActiveX control list box:

To create a list box with multiple selection or extended-selection enabled, use the MultiSelect property. In this case, the LinkedCell property returns a #N/A value. You must use VBA code to process the multiple selections.

Related Question