# Assigning a value to a cell based on conditions of two other cells being equal

1. ## Assigning a value to a cell based on conditions of two other cells being equal

Okay I have tried searching and I am just not smart enough to figure out all this programming stuff so I am not exactly sure what to do, but I assume it is simple.

I have three columns, one is Purchase Size, one is Count Size, and one is Purchase Count.

I just need a formula that will look at the Purchase Size and Count Size and if they are equal automatically force the Purchase Count to be a value of 1. If they are not equal I need it to allow the user to input the correct amount.

Both the Purchase Size and Count Size columns utilize a list of possible selections.

It would be nice to also lock the ability to alter the 1 if the two columns are identical

Thank you very much  Register To Reply

2. ## Re: Assigning a value to a cell based on conditions of two other cells being equal

There are two ways to tackle this. The simplest one, using Excel formulas, may not meet your need to control the data. If the columns are A, B, and C, you can put the following in, say, C2:

=IF(A2=B2,1,IF(D2="Enter Value Here","",D2))

And put the following in D2, currently not used:

=IF(A2=B2,"","Enter Value Here")

Then you unlock the cells in column D and turn on worksheet protection. The user will not be able to change the value in C2. If the user enters something in D2, then use conditional formatting in D2 so that if C2=D2, the font in D2 is white and effectively invisible.

A simple example is attached.

I think this solves your problem with formulas only. However, it has the drawback that once the user has entered a number, the formula is replaced by the value and you can't go back to the prompt, if some data changes. Also, you have to make sure that the formula in Column D is copied down to enough rows to accommodate new data (in my example I have filled it down several rows to start with).

A more sophisticated solution could be developed by writing VBA that would prompt the user to enter a value only when it's needed, and not need to use an extra column to do it.  Register To Reply

3. ## Re: Assigning a value to a cell based on conditions of two other cells being equal

yea I was afraid it would require VBA, im developing this sheet to be completely idiot proof so our customers can't screw something up, because they like to be able to say Case-Case-12. and they dont realize there cant be 12 cases in a case.  Register To Reply