Double answers???????

W

web

Do anyone know how to both have a formula and still be able to put in data in
a cell. Let me specify. For instance in cell A you'll be able to put a value
say between 1-100%, the same time cell B will calculate what is left, i.e.
100%-(vlaue in cell A).
So if you'll put in 10% in A, you'll get 90% in cell B. Okej that's easy,
but if you want to make it possible to use that for both cells, that is to be
able to put in the value 20% in cell B! and get 80% in cell A. So the user
will have freedom of choice to use either cell A or cell B to put in data,
but the formula won't be changed or deleted. Is that possible in Excel?
 
M

Myrna Larson

Not possible with formulas. 3 points:

1. The formulas you are describing would create a circular reference: A1
uses the data in B1 to calculate a result; B1 uses the value in A1 to
calculate a result.

2. If a cell has a formula, when you type a literal number in that cell, it
replaces the formula. You would need a Worksheet_Change event macro to
accomplish this.

3. The macro cannot replace what you typed with the formula that was there
before because of the circular reference issue noted above.

The macro would have to first determine in which column you typed a number,
then calculate the desired formula and put the result in the other cell. The
two conversion formulas (one for each data entry column) to be used would be
hard-coded in the macro.
 
Top