Thread: Data Validation Formula (Excel 2000 SR1)

1. 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. Re: Data Validation Formula (Excel 2000 SR1)

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))

??

3. Re: Data Validation Formula (Excel 2000 SR1)

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

Steve

4. Re: Data Validation Formula (Excel 2000 SR1)

Hi John,

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.

Regards,

5. Re: Data Validation Formula (Excel 2000 SR1)

Hi Steve,

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

Thanks,

6. 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. 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
•