A
apache007
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.
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.