Need to reduce amount when another amount is entered

[johnathon]

hi
i have a cashflow spreadsheet that monitors how much money
i owe and how much i have already paid. there are two
columns of the data i want to validate. basically the
first column is the commitment (amount i need to pay) and
the second column is the cleared amount (the amount that
has already been paid.

for example say there is £1000 in the commitment column,
and i enter £100 in the cleared column. i want the figure
in the first column to then change to £900. this is
becuase i have already paid £100 and now only need to pay
another £900.

i have tried putting a validation rule on the first
column: it was something like this: =IF(B1>0,A1-B1,A1). so
column a was the commitment and column b was the cleared
amount. i had entered this rule in the data validation of
the first column. but this does not work. the number does
not change when you enter an amount in the second column

can any one help???
any ideas would be welcome.
thanks

 

[Ron Rosenfeld]

hi
i have a cashflow spreadsheet that monitors how much money
i owe and how much i have already paid. there are two
columns of the data i want to validate. basically the
first column is the commitment (amount i need to pay) and
the second column is the cleared amount (the amount that
has already been paid.

for example say there is £1000 in the commitment column,
and i enter £100 in the cleared column. i want the figure
in the first column to then change to £900. this is
becuase i have already paid £100 and now only need to pay
another £900.

i have tried putting a validation rule on the first
column: it was something like this: =IF(B1>0,A1-B1,A1). so
column a was the commitment and column b was the cleared
amount. i had entered this rule in the data validation of
the first column. but this does not work. the number does
not change when you enter an amount in the second column

can any one help???
any ideas would be welcome.
thanks

In a third cell someplace, specify your initial commitment (IC). Then the
formula in your Commitment cell would be =IC-Cleared.


--ron