subtract entries from allocated total until it reaches zero

  • Thread starter Thread starter kirbydog
  • Start date Start date
K

kirbydog

I am trying to make numbers entered into a column subtract from a fixed cell
which contains an allocated total, and as the numbers are entered into the
column they subtract from the allocation until it reaches zero.
 
One formulas idea via using a dynamic range

In Sheet1,
Assume the reduction amts will be entered progressively in A2 down to A1000
(w/o intervening blank cells)

Create a dynamic defined range, via Insert>Name>Define:
Names in workbook: Reduction
Refers to:
=OFFSET(Sheet1!$A$2,,,COUNT(Sheet1!$A$2:$A$1000))

Then assuming your allocated total amt is 100,
you can enter something like this in A1: =100-SUM(Reduction)

Test it out .. A1 will monitor & return the desired residual amts
Success? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
 
Back
Top