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