Validation

M

Myriam

I'm trying to validate 3 cells and for some reason I don't seem to get
'validation' to work.
Using cells A1, B1, C1 I want to restrict input if one of the other cells
already has a value, so on cell A1, I validate>Custom>Formula:

=AND(B1=0,C1=0)

but it does not work. (???)

Could you please give me a light and tell me what I'm doing incorrect?
Any help will be highly appreciated!
 
B

Bob Phillips

=AND($B1="",$C1="")

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
C

Casey

Myriam,
The formula worked for me. If both B1 and C1 = 0 I can put any value I
want to in A1. However if either B1 or C1 have a value other than 0, I
get the expected dialog box informing me that values have been
restricted. Is there something else you were trying to do?
 
M

Myriam

Thanks, Bob. This validation should be a no brainer but I can't seem to make
it work. I even opened a clean workbook, placed the formulas and it is still
not validating. I'm using Excel 2003. Any other suggestions?

Casey, I'm just trying to insert a simple validation nothing more. If it is
working for you I must have a problem with Excel itself. Or... is there
something that needs to be 'checked' that I might not have selected
somewhere...?
Thanks.
 
C

Casey

Myriam,
For the life of me I can't think what could be causing it not to work
for you. Are A1, B1 and C1 all numbers or a mixture or numbers and
text? Because the formula would maybe need to be a hybrid of my
suggestion and Bob's if B1 or C1 contained text. I'm stumped
 
B

Bearacade

Validation for A1 should be:
=AND(ISBLANK(B1),ISBLANK(C1))

Validation for B1 should be:
=AND(ISBLANK(A1),ISBLANK(C1))

Validation for C1 should be:
=AND(ISBLANK(A1),ISBLANK(C1))

Make sure that Ignore Blank is NOT checked.

I will attach a sample copy for you.


+-------------------------------------------------------------------+
|Filename: validation.zip |
|Download: http://www.excelforum.com/attachment.php?postid=5180 |
+-------------------------------------------------------------------+
 
M

Myriam

Thanks for the reply (I could not download attachment)
I copied the formula on Validation for A1, unselected "ignore blank", and
the problem now is that it *always* restricts the value ..: (
 
Top