Combine list and custom formula data validation in one cell within excel without vba

Question

Can anyone help with a way of combining list and custom formula data validation in a single cell without using vba? I have a custom formula which validates the data if logical is TRUE but I want the user to have ability as well to select from a drop down list. Any assistance will be appreciated. Thanks.

Sorry if the question wasn't so clear enough. Please see below:

In this example, even though the user is able select from a dropdown list, if the user selects 7 for cell A2, the validation should throw out an error because it is not less than or equal to cell B2. Is it clearer?

Answer 1

This non-VBA solution combines a conditional with a dropdown picklist. It provides 2 different picklists, dependent on value in another column.

This is different than the oft-shared cascading-lists method (but do see the link for some great techniques)

Let's say you have a table with two columns. Field1 may contain anything:

a
box
cat
z

Requirement: Field2's validation should display List1 dropdown list when Field1 contains a, and display List2 when Field1 contains anything else.

Solution: Relies on the fact that an Excel conditional formula can return a range containing a list of items.

Steps:

• Create a list of picklist-values on a lookup-sheet, and name it List1.
• Create another list on the lookup-sheet, and name it List2.

• On your entry-sheet, insert a Table object containing 2 columns. Name the table MyTable, and the fields Field1 and Field2.

  

• Create the following defined name, and call it ValidFmla. This is the secret sauce:

  =IF(MyTable[@Field1]="a", List1, List2)

• Select the entry-cells in Field2, and click Validation on the data-ribbon.

• Choose "List" type, and enter =ValidFmla

• test it out:

  

Benefits:

• It will not break if you change your table name, field names, list-names, or validation-formula name-- all dependent stuff updates automatically.

• supports any complex conditions you want, as long as your validation formula returns a list-range. Validation does not have to be based on the values in Field1.

• the validation list-ranges can be named anything. the dependent-lists method requires naming the validation list-ranges the same as the values in the first field.

Note, you do not have to use a Table object to use this method. But, using the Table object provides structured-referencing in the validation formula, which is a smarter, more readable way to build formulas.

Answer 2

Save we want to setup DV in cell B1 so:

• if a number is entered, it must be less than the value in A1
• if text is entered, it must be in the list from D1 throughD3

Here is the formulaic DV to do that:

The formula combines numeric tests with the ability to restrict input to some fixed list.