inventory values to update

[mc136355]

Hi I am trying to update values in an inventory sheet but if value has no change then i cannot create a formula to show the previous value. Any help would be appreciated. Hope this makes sense. thanks MC

cust1 has starting value of 100
cust2 has starting value of 200

Thanks[TABLE="width: 500"]
<tbody>[TR]
[TD]date
[/TD]
[TD]details[/TD]
[TD]type
[/TD]
[TD]customer
[/TD]
[TD]in
[/TD]
[TD]out
[/TD]
[TD]cust1
[/TD]
[TD]cust2
[/TD]
[/TR]
[TR]
[TD]21/08/19
[/TD]
[TD]order
[/TD]
[TD]AAA
[/TD]
[TD]cust1
[/TD]
[TD]500
[/TD]
[TD]100
[/TD]
[TD]500
[/TD]
[TD]200
[/TD]
[/TR]
[TR]
[TD]21/08/19
[/TD]
[TD]order
[/TD]
[TD]AAA
[/TD]
[TD]cust1
[/TD]
[TD]100
[/TD]
[TD]50
[/TD]
[TD]550
[/TD]
[TD]200
[/TD]
[/TR]
[TR]
[TD]21/08/19
[/TD]
[TD]order
[/TD]
[TD]AAA
[/TD]
[TD]cust2
[/TD]
[TD]50
[/TD]
[TD]100
[/TD]
[TD]550
[/TD]
[TD]150
[/TD]
[/TR]
[TR]
[TD]21/08/19
[/TD]
[TD]order
[/TD]
[TD]AAA
[/TD]
[TD]cust1
[/TD]
[TD]100
[/TD]
[TD]200
[/TD]
[TD]450
[/TD]
[TD]150
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Maybe...


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][td="bgcolor: #DCE6F1"]

H​

[/td][td="bgcolor: #DCE6F1"]

I​

[/td][td="bgcolor: #DCE6F1"]

J​

[/td][td="bgcolor: #DCE6F1"]

K​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

date​

[/td][td]

details​

[/td][td]

type​

[/td][td]

customer​

[/td][td]

in​

[/td][td]

out​

[/td][td]

cust1​

[/td][td]

cust2​

[/td][td][/td][td]

Customer​

[/td][td]

Starting Value​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

21/08/2019​

[/td][td]

order​

[/td][td]

AAA​

[/td][td]

cust1​

[/td][td]

500​

[/td][td]

100​

[/td][td]

500​

[/td][td]

200​

[/td][td][/td][td]

cust1​

[/td][td]

100​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

21/08/2019​

[/td][td]

order​

[/td][td]

AAA​

[/td][td]

cust1​

[/td][td]

100​

[/td][td]

50​

[/td][td]

550​

[/td][td]

200​

[/td][td][/td][td]

cust2​

[/td][td]

200​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

21/08/2019​

[/td][td]

order​

[/td][td]

AAA​

[/td][td]

cust2​

[/td][td]

50​

[/td][td]

100​

[/td][td]

550​

[/td][td]

150​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

21/08/2019​

[/td][td]

order​

[/td][td]

AAA​

[/td][td]

cust1​

[/td][td]

100​

[/td][td]

200​

[/td][td]

450​

[/td][td]

150​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Formula in G2 copied to H2 and down
=VLOOKUP(G$1,$J:$K,2,0)+SUMIF($D$2:$D2,G$1,$E$2:$E2)-SUMIF($D$2:$D2,G$1,$F$2:$F2)

M.

 

[mc136355]

Hi MB thanks for the quick reply. Looks like this has done the trick and is working on both columns. Thanks for your time. MC

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.