Ideas on how to do Stock Excel File
 

Dear Experts,

I am trying to do stock files for my company. The following is the skeleton
of what I have and need suggestions on how to implement it on Excel 2003.

On Sheet "CATEGORY", I have the following tables:

Code Category Name

STAT STATIONARIES
ELECT ELECTRICAL
COMP COMPUTER

On Sheet "DETAILS", I have the following tables:

Stock ID Description

STAT01 Pencil
STAT02 PEN
STAT03 BOOK

COMP01 MONITOR
COMP02 KEYBOARD
COMP03 MOUSE

ELECT01 LAMP
ELECT02 CORDS


On Sheet DEPARTMENT, I have the following tables:

Department

Accounting
Marketing
Sales
IT

I would like to have 2 more sheets that have the following:

1. Sheet Transaction - Daily transaction to RESUPPLY/CUT the stok
2. Sheet Monitor - To Monitor what I have left and how much total.

I need the following coloumn on Sheet TRANSACTION:
- CODE (Drop Down box, contain Category Name, but when selected display CODE)
- STOCK ID (Drop down box contains FILTERED list based on what's selected
on CODE. Let say if the user select COMP, then STOCK ID contains COMPUTER,
KEYBOARD, MOUSE, etc)

- Date Purchased/Date Used (Dates when the stock is purchased/restock or
date when the supply is taken/used)
- Purchased Price (for restocking with different price or perhaps the same
price)
- Department (Departmen that use/request the stocks)

For an example,

I purchased 10 units of MOUSE on 1 May 09 @ $5/each and 20 units of MOUSE
on 5 May 09 @ $10/each.

On sheet MONITOR,

I need to see what stocks I have left (those stocks that are not zero).
So the result will have to be seen something like this:

MOUSE

Date Purchased Price Unit Left
1 May 09 $5 4
5 May 09 $10 19

If MOUSE bought on 1 May 09 is all used up, then that entry is deleted
automatically.

I hope I give enough illustration. I have ponder a while on how to do this
with SUMIFS function and etc, but can't get an effecient solution.

Does anyone has a template file or ideas on how to do this? If possible w/o
VBA programming.

Thanks in advance.