Restiction of cell value
 

I have three (3) cells into which a value (%) can be entered. A 4th cell will
be for the result of a calculation using either only one of those cells or
both of the other cells. For example: A1 or, A2 and A3 will have a percentage
to be calculated using the value in B1 and the result placed in B2. I want
to be able to insert a value in A1 and if so, restrict any value from being
placed in A2 & A3...and vice versa. Here is what I want to do as explained in
English. If A1 has a value, then multiply it by BI and place the result in
B2. If both A2 & A3 have a value, then multiply it by B1 and place the
result in B2. The problem is, I want to place a value only into A1 or only
into both A2 & A3 ! If A1 has a value, then I need to restrict any values
being placed in A2 & A3...and vice versa. Sorry for the long explanation.
Can anyone help?
--
Ken Michaels

Restiction of cell value
 

You can use Data Validation for this.

Select Cell A1
From the Data Menu, choose "Validation"
Change the Allow Field to "Custom"
Uncheck the "Ignore blank" box
Enter the formula: =AND(A2="",A3="")
Click OK
Now Select Cells A2 and A3
From the Data Menu, choose "Validation"
Change the Allow Field to "Custom"
Uncheck the "Ignore blank" box
Enter the formula: =$A$1=""
Click OK
Now, in cell B2, enter the formula: =IF(A1="",(A2+A3)*B1,A1*B1)

You may need to adjust the formula in B2. I'm just assuming that you want
A2 and A3 added together?

Note, that Data Validation only prevents users from manually entering data
into cells. It will not prevent data resulting from a formula, or data being
pasted into the cell.

HTH,
Elkar


"Ken" wrote:

I have three (3) cells into which a value (%) can be entered. A 4th cell will
be for the result of a calculation using either only one of those cells or
both of the other cells. For example: A1 or, A2 and A3 will have a percentage
to be calculated using the value in B1 and the result placed in B2. I want
to be able to insert a value in A1 and if so, restrict any value from being
placed in A2 & A3...and vice versa. Here is what I want to do as explained in
English. If A1 has a value, then multiply it by BI and place the result in
B2. If both A2 & A3 have a value, then multiply it by B1 and place the
result in B2. The problem is, I want to place a value only into A1 or only
into both A2 & A3 ! If A1 has a value, then I need to restrict any values
being placed in A2 & A3...and vice versa. Sorry for the long explanation.
Can anyone help?
--
Ken Michaels