Drop Down List

[Elijah Red]

Is there a way to make a drop down list by ctrl clicking? So if you have information in A1 through A4, but for this specific list you only need A1 and A3; is there a way to skip A2? When I try this a message pops up saying, "You may not use reference operators (such as unions, intersections, and ranges) or array constants for Data Validation criteria."

 

[Nebu]

Hi:

You may want to check.

https://trumpexcel.com/select-multiple-items-drop-down-list-excel/

Thanks

 

[Peter Bartholomew]

I am not sure about the exact situation you describe but the usual way in which I deal with the limitations of calculations performed within data validation is to use a defined name. The formula entered into the refers to box should evaluate to a Boolean scalar value (i.e. TRUE/FALSE or equivalently 0/1). The calculation options for a named formula are more comprehensive than any other calculation environment in Excel, including placing formulas within the worksheet itself. By that, I mean that calculations performed by Named Formulae are always array calculations and some old XLM commands such as EVALUATE work there but not on the worksheet. The name is accepted for data validation even when the calculation it performs cannot be used.