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 validateCustomFormula:

=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!

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

--
HTH

Bob Phillips

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

"Myriam" wrote in message
...
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 validateCustomFormula:

=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!

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?


--
Casey


------------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545
View this thread: http://www.excelforum.com/showthread...hreadid=570872

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.

"Casey" wrote:


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?


--
Casey


------------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545
View this thread: http://www.excelforum.com/showthread...hreadid=570872

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


--
Casey


------------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545
View this thread: http://www.excelforum.com/showthread...hreadid=570872

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

--
Bearacade


------------------------------------------------------------------------
Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
View this thread: http://www.excelforum.com/showthread...hreadid=570872

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 ..: (

"Bearacade" wrote:


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

--
Bearacade


------------------------------------------------------------------------
Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
View this thread: http://www.excelforum.com/showthread...hreadid=570872

Myriam, follow the bottom link on the original post

http://www.excelforum.com/showthread...hreadid=570872

and download it from there.

I am not having that problem, it is allowing me to put any value in any
of the 3 cell as long as the other 2 is empty.. try it and see if it
works


--
Bearacade


------------------------------------------------------------------------
Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
View this thread: http://www.excelforum.com/showthread...hreadid=570872

Works perfect. Thanks.

"Bearacade" wrote:


Myriam, follow the bottom link on the original post

http://www.excelforum.com/showthread...hreadid=570872

and download it from there.

I am not having that problem, it is allowing me to put any value in any
of the 3 cell as long as the other 2 is empty.. try it and see if it
works


--
Bearacade


------------------------------------------------------------------------
Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
View this thread: http://www.excelforum.com/showthread...hreadid=570872