Stock Control/Data Archiving

R1G1C1D3

New Member
Joined
Aug 7, 2009
Messages
3
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. :)
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

phxsportz

Well-known Member
Joined
Jun 11, 2006
Messages
1,985
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>

29c3vp2.jpg


kb3qz7.jpg


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

New Member
Joined
Aug 7, 2009
Messages
3
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,685
Messages
5,654,746
Members
418,149
Latest member
amamiche67

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top