+ Reply to Thread
Results 1 to 4 of 4

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

  1. #1
    Registered User
    Join Date
    06-26-2013
    Location
    USA
    MS-Off Ver
    Windows 7
    Posts
    45

    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. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,525

    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?
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    12-11-2014
    Location
    Dallas, TX
    MS-Off Ver
    2010
    Posts
    94

    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. #4
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,009

    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
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 4
    Last Post: 11-17-2013, 12:05 PM
  2. [SOLVED] How to USE """"" cells count """"" change font color
    By austin123456 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-09-2013, 06:14 AM
  3. Replies: 0
    Last Post: 01-09-2013, 06:52 PM
  4. [SOLVED] Data validation: allow entry into a cell if other three cells have "X", "Y" and "Z"?
    By RogerRangeRover in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-05-2013, 04:49 AM
  5. Replies: 3
    Last Post: 02-16-2011, 02:55 PM

Bookmarks

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