Wednesday, June 28, 2017

data validation - Multi-value selection in an Excel cell


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?


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.



No comments:

Post a Comment

hard drive - Leaving bad sectors in unformatted partition?

Laptop was acting really weird, and copy and seek times were really slow, so I decided to scan the hard drive surface. I have a couple hundr...