Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error when creating a stock chart from data in .CSV file | Excel Discussion (Misc queries) | |||
How do I make a web query file to download stock data? | New Users to Excel | |||
How can I use Dutch (NL) stock MSN MoneyCentral Stock in Excel | Excel Worksheet Functions | |||
Office 2003 Stock Action Add-In - Stock Handling Capacity/Numbers | Excel Worksheet Functions | |||
STOCK CONTROL IDEAS | Excel Discussion (Misc queries) |