Hi shone try this, the only way i no:

Rightclick on sheet-SUPPLIED-tap
select Show Programcode
paste this kode in window to the right:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C2:C20")) Is Nothing Then Exit Sub
If Target = "" Then Exit Sub

Dim r, Code, Checknr

Checknr = ""
Code = Target.Offset(0, -1)

If Code = "" Then Target = "": Target.Offset(0, -1).Select: Exit Sub

For r = 2 To 20
If Sheets("STOCK").Cells(r, 1) = Code Then
Checknr = "ok"
Sheets("STOCK").Cells(r, 2) = Sheets("STOCK").Cells(r, 2) + Target.Value
End If
Next

If Checknr = "" Then
Target.Select
MsgBox ("No match in Database"): Target = ""
Sheets("DATABASE").Select
End If

End Sub


and put this in sheet SOLD kode-window

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C2:C20")) Is Nothing Then Exit Sub
If Target = "" Then Exit Sub

Dim r, Code, Checknr

Checknr = ""
Code = Target.Offset(0, -1)

If Code = "" Then Target = "": Target.Offset(0, -1).Select: Exit Sub

For r = 2 To 20
If Sheets("STOCK").Cells(r, 1) = Code Then
Checknr = "ok"
Sheets("STOCK").Cells(r, 2) = Sheets("STOCK").Cells(r, 2) - Target.Value
End If
Next

If Checknr = "" Then
Target.Select
MsgBox ("No match in Database"): Target = ""
Sheets("DATABASE").Select
End If

End Sub

The kode check for quantity and if CD-Code nr is in DATABASE

best regards. PM

Now im avake i realised there is another way :-)

Paste this formula in Sheet-STOCK B2 and copy down
=SUMPRODUCT((SUPPLIED!B2:B20=STOCK!A2)*(SUPPLIED!C 2:C20))-SUMPRODUCT((SOLD!B2:B20=STOCK!A2)*(SOLD!C2:C20))

@EXCELENT

ThankYou ;)
Works perfect!

(I like 2nd solution more, pretty straight forward).


--
shone
------------------------------------------------------------------------
shone's Profile: http://www.excelforum.com/member.php...o&userid=35126
View this thread: http://www.excelforum.com/showthread...hreadid=573300