Results 1 to 7 of 7

20021126, 18:44 #1
 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,

20021126, 19:35 #2
 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

20021126, 19:38 #3
 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

20021126, 20:15 #4
 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,

20021126, 20:23 #5
 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,

20021126, 20:38 #6
 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

20021126, 21:08 #7
 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,