Data Validation Formula (Excel 2000 SR1)

Topic

New Reply

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,

Reply | Quote

Replies

[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,anycontent,ABS(number),-ABS(number))

??

Reply | Quote

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,

Reply | Quote

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

=IF(UPPER(A3)=”X”,B30,NOT(ISBLANK(B3))))

Steve

Reply | Quote

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,

Reply | Quote

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 =

Steve

Reply | Quote

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,

Reply | Quote