In D3: =IF(A3="",C3,"")
In E3: =IF(D3<"",LOOKUP(10^10,$A$2:A3),"")

Select D3:E3 and copy down as far as needed


"hankach" wrote:


Hello everyone,

If
A1=Debit; B1= Credit; C1= Sold
A2=100; A5=50; A7=10
B3= 20; B4= 50; B6= 40; B8=40
C2=A2; C3=C2+A3-B3; C4=C3+A4-B4; C5=C4+A5-B5; C6= C5+A6-B6;
C7=C6+A7-B7

I need:

1) A formula in D3 and to be able to copy it down all along my
transactions, that calculates the remaining sold to be consumed from
the first Debit, but if the first debit was totally consumed, it shall
diminish the difference from the second debit entry and so on ..

2) A formula in E3 to return the cell reference of the Debit that D3
refers to.


Conclusion:

Given the data ahead the results should come as follows:
D3= 80 E3=A2 means there still 80 from A2 value to consume it totally.
D4= 30 E4=A2 means there still 30 from A2 value to consume it totally.
D6= 40 E6=A5 means there still 40 from A5 value to consume it totally
(and definitely A2 value was totally consumed since 30 out of the 40 of
B6 cleared the first 100, and so on ..


Thank you for your support




--
hankach