Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Data Validation Formula (Excel 2000 SR1)

    What I need to do (if it's possible) is to set up validation in a series of cells as follows: If a different cell contains "X", then the active cell gets only a positive number; if the different cell contains "Y", then the active cell gets only a negative number; if the different cell contains anything else the active cell can have any content.

    As always, I will be grateful for any assistance here.

    Thanks,

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Data Validation Formula (Excel 2000 SR1)

    [Edit]I misread this as a conditional formatting problem. What data can be input, and is it your intent to convert it along the lines of the formula I suggested?

    I don't quite follow in terms of where the number to be made pos. or neg. only comes from, but something like "Formula Is"

    =CHOOSE(1+(argsource="X")+(argsource="Y")*2,anycon tent,ABS(number),-ABS(number))

    ??
    -John ... I float in liquid gardens
    UTC -7ąDS

  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 Formula (Excel 2000 SR1)

    How about this: col A has the "other cells" and COlb has the validations:

    =IF(UPPER(A3)="X",B3<0,IF(UPPER(A3)="Y",B3>0,NOT(I SBLANK(B3))))

    Steve

  4. #4
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation Formula (Excel 2000 SR1)

    Hi John,

    Thanks for your response.

    Yes I had thought you were setting up a conditional format. The active cell gets its content from users keying in numbers. I need to restrict their inputs along the lines I mentioned in my original post (ie, where "X" in the reference cell restricts the user to entering a positive number and "Y" in the reference cell restricts the user to entering a negative number and anything else in the reference cell imposes no restrictions on the active cell).

    Actually, the formula that Steve offered, below, works.

    Thanks again for your efforts.

    Regards,

  5. #5
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation Formula (Excel 2000 SR1)

    Hi Steve,

    Thank you for your suggestion. Your formula works to perfection.

    I'm curious about the last argument (...NOT(ISBLANK...). Could you explain what it does?

    Thanks,

  6. #6
    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 Formula (Excel 2000 SR1)

    Datavalidation formulas must give a result as "true" to validate
    You wanted any entry (I assumed not a blank) so the formula NOT(isblank(b2)) would be true if B2 contained something number, string, etc.

    FYI, zero is only acceptable if not an X nor Y is entered. since it is neither pos or neg. If you want it as an acceptable ansere change one of the entries to <= or >=

    Steve

  7. #7
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation Formula (Excel 2000 SR1)

    Thank you for the explanation. I had thought that argument was an elegant way to make that last argument evaluate to TRUE, but I wasn't sure why it was necessary. I had figured out the first part of the answer before my original post, but I just couldn't get that last part.

    Thanks again,

Posting Permissions

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