# Data Validation: Allow values "x", "y", or "z" in a cell only if another cell is filled

1. ## Data Validation: Allow values "x", "y", or "z" in a cell only if another cell is filled

I would only like to be able to fill cell B2 with values "x", "y", OR "z" only if A2 is filled with any value.

I have come up with a solution, but I don't like it. It's very long. Currently I have this Data Validation in B2: =OR(IF(\$A2<>"",\$B2="x"),IF(\$A2<>"",\$B2="y")).

The problem is that I have 32 unique "xyz's". Could I do anything with a named range? My attempts thus far, have not proved fruitful.

2. ## Re: Data Validation: Allow values "x", "y", or "z" in a cell only if another cell is fille

With your 32 acceptable values in H1:H32

Custom and uncheck "Ignore Blank"

Formula
=AND(LEN(A2)>0,ISNUMBER(MATCH(B2,\$H\$1:\$H\$32,0)))

Does that work for you?

3. ## Re: Data Validation: Allow values "x", "y", or "z" in a cell only if another cell is fille

This should also work, and may be easier to customize. Create a Data Validation List, and use this as your source:

=IF(A2="","",\$H\$1:\$H\$32)

4. ## Re: Data Validation: Allow values "x", "y", or "z" in a cell only if another cell is fille

=AND(LEN(A2)>0,LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"x",""),"y",""),"z",""))=0)
try this

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1