Prevent data entry and calculate at same time.

[Gordsky]

Hi,

I have a spreadsheet which contains a cell for hours worked and a cell for the percentage of full time that represents
ie, a1=hours worked and b1=the full time percentage that equates to.
I can get them to work fine if I make input possible into one only and use a formula in the second however what i'm after is the ability to enter into either cell and the other cell will calculate its value.

a good example of what I'm trying to achieve is googles converter. so if a value is entered into "stone" cell it calculates the value in the "kilo gram" but if you then type a second value into the KG accordingly it adjusts the stone cell accordingly.

No matter what I try I always get a circular or loop error.
Any suggestions

[LJMetzger]

Hi Gordsky,

Excellent question. The problem is that without using VBA, a cell can either be a value or a formula, but not both.

One way to solve your problem is to add a 3rd cell which is the only place data is entered.
If the value in the 3rd cell has the last character as %, your % cell will take that value, and you can calculate hours for the other cell.
If the value in the 3rd cell has no % as the last character, your hours cell will take that value, and you can calculate % for the other cell.

Unfortunately, your simple formula will become a little more complicated.

Many problems like yours can be solved using a 'helper' cell or 'helper' column.

Lewis