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.

Answer 3

I know this thread is older, but for completeness I am also going to offer another solution that is non-VBA. The OP I don't believe was seeking methods to provide dependent lists as per most of the solutions offered, instead he wants his options list to exist as-is and instead provide additional validation on the actual selection being performed.

This actually needs to use a different feature of Excel to provide this insight to yourself easier. Conditional Formatting. As per the OP's screenshot provided originally:

Highlight column A, click Conditional Formatting (Home Tab for me) > New Rule. Select the last option, custom formula one, and use the formula:

=$A1>$B1

Click Formatting button, change the background or some other colouring so the validation is made obvious, click OK. See screenshot for results, you still setup your data validation list as per normal for the user to select values from. One thing to note, is that this does not stop the input, it merely provides a visual confirmation that the input is wrong.

Screenshot of results