• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Drop Down List

Elijah Red

New Member
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."

Peter Bartholomew

Well-Known Member
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.