Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Data Validation Problem (Excel 200)

    I am using this formula for data validation

    =IF(C31="DIRECT BUSINESS",C34=0,C34<25)

    If C31 = "DIRECT BUSINESS", I Can only enter 0 in C34. this bit works fine.

    However if C31 does not contain "DIRECT BUSINESS", I want to limit C34 to to a maximum of 25 this bit does not work.

    Any ideas please.

    I have also tried the following

    =OR(AND(C31="DIRECTBUSINESS",C34=0),AND(C31<>"DIRE CT BUSINESS",C34<25))

    =IF(C31="DIRECTBUSINESS",C34=0,AND(C34<25,C34>0))

    But they all allow me to enter more than 25 in C34 If C31 does not contain "Direct Business"

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data Validation Problem (Excel 200)

    Does it help if you clear the "Ignore Blank" check box in the Data | Validation dialog?

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Data Validation Problem (Excel 200)

    It seems to work for me in XL2002. (see the attached file)

    Steve

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data Validation Problem (Excel 200)

    But Alan has Excel 200 <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    If the "Ignore Blank" check box is ticked, the validation will not kick in if C31 is blank.

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Data Validation Problem (Excel 200)

    HI Steve

    I am left with egg on my face, because the cell was formatted to % <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    Thank to you and Hans for your reply.

    Braddy
    If you are a fool at forty, you will always be a fool

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Data Validation Problem (Excel 200)

    HI Hans

    I have to save up and buy a later version <img src=/S/grin.gif border=0 alt=grin width=15 height=15> , please see my reply to Steve.

    Many thanks once again

    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

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