Calculations

[shone]

Hi to all

I'm running a stall on which I'm selling CD's.
Every CD has it's own, unique code number (#1000, #1001, #1002...) i
database (sheet #DATABASE).
I have following sheets:

- STOCK (current quantity of CD's by code number on my stall)
- DATABASE (database with informations about price, suppliers an
titles)
- SUPPLIED (CD's suppliers are bringing to me)
- SOLD (CD's sold to customers)


// Problem:

Supplier brings me 5x titles with code number #1000. I type it int
sheet #SUPPLIED,
which can consists of three columns (date, code number and quantity)
as well as sheet #SOLD.
Before I type it, I first check if title with that code number exist
in sheet #DATABASE,
if not I first type complete informations regarding it manually.
Before supplier brought me 5x titles with code number #1000,
I already had 3 of the same kind on my stock.

Now, when I type 5x CD with code number #1000 into sheet #SUPPLIED,
sheet #STOCK should lookup for rows and if it finds that CD title wit
that code number
#1000 exists on stock, it should add 5 to existing 3. Also, when I sel
1x CD to customer,
with code number #1000 it should substract quantity (8-1=7).

Sheet #STOCK consists of 3 columns (code number, quantity and price).
Let's say that I can solve 1st and 3rd column with VLOOKUP formula bu
quantity is what's bothering me.

I keep bumping my head for hours and can't figure it out...

Any tip?

Here is link to example file if attached does'nt working:
http://www.expertinternet.org/xls/STOCK.ZI

+-------------------------------------------------------------------
|Filename: stock.zip
|Download: http://www.excelforum.com/attachment.php?postid=5236
+-------------------------------------------------------------------

 

[Guest]

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

 

[Guest]

Now im avake i realised there is another way

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

 

[shone]

@EXCELEN

ThankYou
Works perfect!

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