Consulting

Results 1 to 8 of 8

Thread: Solved: Two validation in one cell

  1. #1

    Solved: Two validation in one cell

    Suppose cell A2 I want a drop down list via Data\validation\Allow\List say x,y,z At the same time another validation rule is needed if A1 is blank no entry can be made in A2 for which validation rule is Data\Validation\Allow\Custom =NOT(COUNTBLANK(A1))
    How both these validation can be made??????????

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,147
    Location
    Maybe this will do it for you

    =OR(AND(A1="",A2<>""),AND(A1<>"",OR(A2="x",A2="y",A2="z")))

  3. #3
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,776
    Use a dynamic range for the Validation list.

    If your validation list came from cells Sheet1 C1:C10 (for example)

    Define a name=MyList RefersTo: =OFFSET(Sheet1!$C$1,0,0,10,1).
    Then incorperate a factor that varies with A1's emptyness.

    myList = OFFSET(Sheet1!$C$1,0,0,10*(1-ISBLANK($A$1)),1)

    When A1 is not blank, myList has 10 rows.
    When A1 is blank, myList has 0 rows.

    Set the Validation to List Source: =MyList

  4. #4
    Dear xld, tested your formula in cell A2 Data\validation\aloow\custom but it restricts users to enter any data except x,y,z, in case cell A1 is not blank ie
    1. if cell A1 is blank, any data can be entered in A2 whereas I need that if A1 is blank no any data cannot be entered in cell A2
    2.If cell A1 is not blank then only x,y,z, can be entered. This is correct & confirm my needs.
    Plz try something to fix problem1

  5. #5
    Dear mikerickson, I could not understand your tips. Would u post a sample workbook?

  6. #6
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,776
    Get your data Validation from a dynamic named range. Define that range in such a way that it has no rows when A1 is blank.

  7. #7
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,147
    Location
    Change the formula to

    =OR(AND(A1="",A2=""),AND(A1<>"",OR(A2="x",A2="y",A2="z")))

    and uncheck the Ignore Blank checkbox.

  8. #8
    Thanks xld, the formula is fully working.

Posting Permissions

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