• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Related Cells

G

grumpus

New Member
This one should be easy to explain but I have not figured out the solution. So I'm back climbing the Chandoo mountain seeking the advice of the wise sages who live atop it.


I have 2 cells, for simplicity A2 and B2. These cells are related to each other in that if there is a value is entered into A1 then I want to prevent a value from being entered into B1. Correspondingly, if a value is entered into B1, I need to prevent an entry in A1.


Think about it this way...the heading in A1 could be "Ketchup" and B1 could be "Mustard". The user could enter the number of packets they want in A2 or B2 but you are allowed to choose only Ketchup or Mustard...not both.


Hopefully this makes sense...thanks in advance for your assistance.
 
With A2:B2 selected, Data Validation, Custom Rule.

=COUNTA($A$2:$B$2)<2


This should force the outcome that you want where you can only have 1 of the cells contain a value. If you're only dealing with numerical entries, I might recommend using the COUNT function instead of COUNTA, but it doesn't make that big a difference.
 
I tried this with cells A2:B2 empty and then tried to enter a number into either of those two cells and received an error message, "The value you entered is not valid. A user has restricted the values that can be entered into this cell." I'm using Excel 2007 and tried both the COUNTA and the COUNT.
 
Hi

What Luke M showed you it's what you want. The user can write in only one of the two cells.

If A2 not blank and you try to write something in B2, you will have the error message.


Note the you can personnalize the message error (see the 2 tabs of the winodws about data validation)


Regards
 
Possible issues:

Are all cells involved static values, or are there actually formulas being used?

Did you remember to put the dollar signs in the formula?

Make sure that the formulas has the "less than" symbol, not the "greater than" symbol.
 
Hi, grumpus!

Considered uploading the workbook? Give a look at the second green sticky post at this forums main page for uploading guidelines.

Regards!
 
Thanks Luke and mercatog! I tried opening a new file and started anew and then it worked...must have been some conflict somewhere in the file because I was copying and pasting the formula. Much appreciated!
 
You must log in or register to reply here.
Back
Top