Stock Control/Data Archiving

[R1G1C1D3]

Ok. I am using Excel 2007, and am currently compiling an invoice system for my business. I have an invoice on which the user enters the list of stock numbers of items being purchased in one column (which trigger information about the items to be displayed through v-lookups) and a second column that has the quantity being purchased. In a second sheet, there is a table style section that has (in columns) Stock number, opening stock, and current stock.

Is there any way of a) having a button that stores the Name, adress, etc of the person buying, as well as the stock and the corresponding quantities of each that they bought all in one row, in such a way that when the next order is saved, the previous one is still there and b) taking the quantity that was bought of each item and subtracting it from opening stock to get current stock, and doing this for each item, so that I have a list of how much of every item I have, and how much I started with.

It is a big ask, and I will be incredibly grateful if some excel-wizard could resolve my problem.

 

[phxsportz]

This is a widely generic question.. however, I have a liquor control system that I use that stores historical data regarding usage and transfers from one of my bars to another.. When ever a burned bottle is entered or a quantity of liquor is transfered I store the transaction in a separate worksheet and then adjust me quantity on hand accordingly..

Here's some small screen shots..

<b>Bottle Data</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:128px;" /><col style="width:128px;" /><col style="width:64px;" /><col style="width:100px;" /><col style="width:65px;" /><col style="width:85px;" /><col style="width:128px;" /><col style="width:101px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffcc99; text-align:center; ">Date Shift</td><td style="background-color:#ffcc99; text-align:center; ">Liquor Name</td><td style="background-color:#ffcc99; text-align:center; ">Bottles In</td><td style="background-color:#ffcc99; text-align:center; ">Invoice #</td><td style="background-color:#ffcc99; text-align:center; ">Bottles Out</td><td style="background-color:#ffcc99; text-align:center; ">Reason</td><td style="background-color:#ffcc99; text-align:center; ">X-fer To</td><td style="background-color:#ffcc99; text-align:center; ">Liquor Name</td><td style="background-color:#ffcc99; text-align:center; ">Bottles In</td><td style="background-color:#ffcc99; text-align:center; ">Invoice #</td><td style="background-color:#ffcc99; text-align:center; ">Bottles Out</td><td style="background-color:#ffcc99; text-align:center; ">Reason</td><td style="background-color:#ffcc99; text-align:center; ">X-fer To</td></tr><tr style="height:26px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#c0c0c0; color:#ffffff; text-align:center; ">1</td><td style="background-color:#c0c0c0; color:#ffffff; text-align:center; ">2</td><td style="background-color:#c0c0c0; color:#ffffff; text-align:center; ">3</td><td style="background-color:#c0c0c0; color:#ffffff; text-align:center; ">4</td><td style="background-color:#c0c0c0; color:#ffffff; text-align:center; ">5</td><td style="background-color:#c0c0c0; color:#ffffff; text-align:center; ">6</td><td style="background-color:#c0c0c0; color:#ffffff; text-align:center; ">7</td><td style="background-color:#c0c0c0; color:#ffffff; text-align:center; ">8</td><td style="background-color:#c0c0c0; color:#ffffff; text-align:center; ">9</td><td style="background-color:#c0c0c0; color:#ffffff; text-align:center; ">10</td><td style="background-color:#c0c0c0; color:#ffffff; text-align:center; ">11</td><td style="background-color:#c0c0c0; color:#ffffff; text-align:center; ">12</td><td style="background-color:#c0c0c0; color:#ffffff; text-align:center; ">13</td></tr><tr style="height:26px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:center; ">01/01/09_AM</td><td style="text-align:center; ">Bacardi</td><td > </td><td > </td><td style="text-align:center; ">1</td><td style="text-align:center; ">Sold</td><td > </td><td style="text-align:center; ">Hennessy</td><td > </td><td > </td><td style="text-align:center; ">1</td><td style="text-align:center; ">Empty</td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td

<b>Invoice Data</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:148px;" /><col style="width:105px;" /><col style="width:76px;" /><col style="width:64px;" /><col style="width:110px;" /><col style="width:102px;" /><col style="width:85px;" /><col style="width:93px;" /><col style="width:113px;" /><col style="width:94px;" /><col style="width:85px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td><td >N</td><td >O</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >_09876545433</td><td style="text-align:right; ">5/1/2028</td><td style="text-align:right; ">$235.75 </td><td >Youngs Market</td><td > </td><td style="text-align:right; ">5/25/2009</td><td style="text-align:right; ">6/19/2009</td><td > </td><td >Bacardi</td><td style="text-align:right; ">1</td><td > </td><td style="text-align:right; ">$235.75 </td><td > </td><td > </td><td style="text-align:right; ">$235.75 </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >_3883829827</td><td style="text-align:right; ">5/20/2009</td><td style="text-align:right; ">$100.00 </td><td >Youngs Market</td><td > </td><td style="text-align:right; ">5/25/2009</td><td style="text-align:right; ">6/19/2009</td><td > </td><td >Bacardi</td><td style="text-align:right; ">1</td><td > </td><td style="text-align:right; ">$100.00 </td><td > </td><td > </td><td style="text-align:right; ">$100.00 </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >_99999999999999</td><td style="text-align:right; ">1/1/2009</td><td style="text-align:right; ">$100.00 </td><td >Southern Wine & Spirits</td><td > </td><td style="text-align:right; ">6/11/2009</td><td style="text-align:right; ">6/19/2009</td><td > </td><td >1800 Reposado</td><td > </td><td style="text-align:right; ">1</td><td > </td><td > </td><td style="text-align:right; ">$100.00 </td><td style="text-align:right; ">$100.00 </td></tr></table>

<b>Liquor Data</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:150px;" /><col style="width:93px;" /><col style="width:159px;" /><col style="width:94px;" /><col style="width:64px;" /><col style="width:45px;" /><col style="width:61px;" /><col style="width:165px;" /><col style="width:36px;" /><col style="width:36px;" /><col style="width:62px;" /><col style="width:61px;" /><col style="width:161px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td><td >N</td><td >O</td><td >P</td><td >Q</td></tr><tr style="height:52px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffcc99; font-weight:bold; text-align:center; ">Name</td><td style="background-color:#ffcc99; font-weight:bold; text-align:center; ">Bar Code</td><td style="background-color:#ffcc99; font-weight:bold; text-align:center; ">Description</td><td style="background-color:#ffcc99; font-weight:bold; text-align:center; ">Class</td><td style="background-color:#ffcc99; font-weight:bold; text-align:center; ">Type</td><td style="background-color:#ffcc99; font-weight:bold; text-align:center; ">Size</td><td style="background-color:#ffcc99; font-weight:bold; text-align:center; ">Charged Price</td><td style="background-color:#ffcc99; font-weight:bold; text-align:center; ">Supplier</td><td style="background-color:#ffcc99; font-weight:bold; text-align:center; ">QOH</td><td style="background-color:#ffcc99; font-weight:bold; text-align:center; ">ROP</td><td style="background-color:#ffcc99; font-weight:bold; text-align:center; ">Case Cost</td><td style="background-color:#ffcc99; font-weight:bold; text-align:center; ">Bottles Per Case</td><td style="background-color:#ffcc99; font-weight:bold; text-align:center; ">Image File</td><td style="background-color:#ffcc99; font-weight:bold; text-align:center; ">Location</td><td style="background-color:#ffcc99; font-weight:bold; text-align:center; ">Date Created</td><td style="background-color:#ffcc99; font-weight:bold; text-align:center; ">Date Edited</td><td style="background-color:#ffcc99; font-weight:bold; text-align:center; ">Record Active</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >1800 Reposado</td><td style="font-family:Arial Narrow; font-size:8pt; text-align:right; ">721059221009</td><td >1800 80 Proof</td><td >Call</td><td >Tequila</td><td style="text-align:right; ">1.00</td><td style="text-align:right; ">$5.25</td><td >Youngs Market</td><td style="text-align:right; ">12.5</td><td style="text-align:right; ">2</td><td style="text-align:right; ">$161.55</td><td style="text-align:right; ">6</td><td >1800.jpg</td><td > </td><td style="text-align:right; ">06/09/09</td><td style="text-align:right; ">06/23/09</td><td style="text-align:right; ">TRUE</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >3 Amigos Anejo</td><td style="font-family:Arial Narrow; font-size:8pt; "> </td><td >Tequila</td><td >Call</td><td >Tequila</td><td style="text-align:right; ">0.75</td><td style="text-align:right; ">$5.75</td><td >Hensley</td><td style="text-align:right; ">8.5</td><td style="text-align:right; ">1</td><td style="text-align:right; ">$1.00</td><td style="text-align:right; ">12</td><td >3 Amigos Anejo.jpg</td><td > </td><td style="text-align:right; ">06/22/09</td><td style="text-align:right; ">06/23/09</td><td style="text-align:right; ">TRUE</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >3 Amigos Blanco</td><td style="font-family:Arial Narrow; font-size:8pt; "> </td><td >Tequila</td><td >Call</td><td >Tequila</td><td style="text-align:right; ">0.75</td><td style="text-align:right; ">$5.75</td><td >Hensley</td><td style="text-align:right; ">0.0</td><td style="text-align:right; ">1</td><td style="text-align:right; ">$1.00</td><td style="text-align:right; ">12</td><td >3 Amigos Blanco.jpg</td><td > </td><td style="text-align:right; ">06/23/09</td><td > </td><td style="text-align:right; ">TRUE</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >Absolut</td><td style="font-family:Arial Narrow; font-size:8pt; "> </td><td >Premium Vodka</td><td >Call</td><td >Vodka</td><td style="text-align:right; ">1.00</td><td style="text-align:right; ">$4.00</td><td >Republic Beverage</td><td style="text-align:right; ">4.5</td><td style="text-align:right; ">6</td><td style="text-align:right; ">$1.00</td><td style="text-align:right; ">12</td><td >Absolut.jpg</td><td > </td><td style="text-align:right; ">06/22/09</td><td > </td><td style="text-align:right; ">TRUE</td></tr></table>

All these sheets work together to control a master sheet called Liquor Data which holds the record for each bottle... and All historical data is contained in different worksheets..

such as Invoice Entry sheet, Bottle Data Sheet etc

Hope this points you in the right direction...

 

[R1G1C1D3]

Thankyou very much for your input - eventually solved it with minimum macro use - just had a middle page that laid everything out horizontally, and I just used a macro to paste special just the values across. Worked a treat, but like I said, thanks very much none-the-less for your help, it was greatly appreciated.

 

[R1G1C1D3]

Close thread please, problem solved.