Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculations
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...) in 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 and 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 into 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 exists 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 with that code number #1000 exists on stock, it should add 5 to existing 3. Also, when I sell 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 but 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.ZIP +-------------------------------------------------------------------+ |Filename: stock.zip | |Download: http://www.excelforum.com/attachment.php?postid=5236 | +-------------------------------------------------------------------+ -- shone ------------------------------------------------------------------------ shone's Profile: http://www.excelforum.com/member.php...o&userid=35126 View this thread: http://www.excelforum.com/showthread...hreadid=573300 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculations
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculations
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)) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculations
@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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
order of calculations in Excel | Excel Worksheet Functions | |||
complex calculations | Excel Discussion (Misc queries) | |||
Calculations based on PivotTable information | Excel Discussion (Misc queries) | |||
Calculations crossing multiple sheets | Excel Discussion (Misc queries) | |||
time interval calculations in excel | Excel Discussion (Misc queries) |