Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Help! Combining Validation and Formula

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Ontario
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I would like to do the following:
    if a1=0,then default b5 to X, else, have dropdown list in b5, listing Y and Z as possible values.

    I bow down to your expertise...........

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,645
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-03 11:53, Big Blue wrote:
    I would like to do the following:
    if a1=0,then default b5 to X, else, have dropdown list in b5, listing Y and Z as possible values.

    I bow down to your expertise...........
    Probably not quite what you want, but you could X in some cell say in a sheet called Admin. Select this cell and name it List1. Put underneath this X-cell, Y and Z. Select the cells with Y and Z and name the selection List2.

    In data validation that you set up in B5 opt for List for Allow and enter the following formula:

    =IF(A1=0,List1,List2)

    List1 will be active if A1 is empty or houses the number 0. If a real 0 must be the value of A1 for X, then use:

    =IF(AND(LEN(A1)=0,A1=0),List1,List2)

    Aladin


  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Ontario
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    So close! Thankyou!
    My problem is that if the user doesn't remember to fill in cell a1,(ie a1 =0) I want cell b5 to default to X automatically. If the user does fill in cell a1, then I want to offer dropdown list1 in B5.

    Your solution is great, but it requires user to enter default in B5 by selecting from "other" list.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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