Simple Stock Ledger

Arshmaan

New Member
Joined
Dec 9, 2011
Messages
8
Hello all,

I need your help creating a stock ledger. I have an excel workbook with 3 sheets. In sheet1 I have list of items something like that:-


Sheet1

<table style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:186px;"><col style="width:72px;"><col style="width:106px;"><col style="width:176px;"><col style="width:118px;"></colgroup><tbody><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></tr><tr style="height:26px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td style="font-weight:bold; text-align:center; ">Particulars</td><td style="font-weight:bold; text-align:center; ">Item</td><td style="font-weight:bold; text-align:center; ">Group</td><td style="font-weight:bold; text-align:center; ">Code</td><td style="font-weight:bold; text-align:center; ">Opening Balance</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td>Nawazan</td><td>Drop Card</td><td>Literature</td><td>Nawazan 1/Oct/2011</td><td style="text-align:right; ">1120</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td>LS Minerals</td><td>Drop Card</td><td>Literature</td><td>LS Minerals 1/Oct/2011</td><td style="text-align:right; ">1546</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td>Predivet</td><td>Drop Card</td><td>Literature</td><td>Predivet 1/Oct/2011</td><td style="text-align:right; ">4424</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td>Naflor Inj.</td><td>Drop Card</td><td>Literature</td><td>Naflor Inj. 1/Oct/2011</td><td style="text-align:right; ">2146</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">6</td><td>Nawazan Bol.</td><td>Drop Card</td><td>Literature</td><td>Nawazan Bol. 1/Oct/2011</td><td style="text-align:right; ">2212</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">7</td><td>Vimsel Inj.</td><td>Drop Card</td><td>Literature</td><td>Vimsel Inj. 1/Oct/2011</td><td style="text-align:right; ">2154</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">8</td><td>Amovet LA</td><td>Drop Card</td><td>Literature</td><td>Amovet LA 1/Oct/2011</td><td style="text-align:right; ">2214</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">9</td><td>Aminowan</td><td>Drop Card</td><td>Literature</td><td style="text-align:right; ">Aminowan 1/Oct/2011</td><td style="text-align:right; ">1125</td></tr></tbody></table>

In sheet2 I have data entry work:-


Sheet2

<table style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:70px;"><col style="width:64px;"><col style="width:87px;"><col style="width:145px;"><col style="width:75px;"><col style="width:61px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:355px;"></colgroup><tbody><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></tr><tr style="height:34px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td style="font-weight:bold; text-align:center; ">Dated</td><td style="font-weight:bold; text-align:center; ">Month</td><td style="font-weight:bold; text-align:center; ">Year</td><td style="font-weight:bold; text-align:center; ">Item Sent</td><td style="font-weight:bold; text-align:left; ">Reference</td><td style="font-weight:bold; text-align:center; ">Team</td><td style="font-weight:bold; text-align:center; ">Opening
Balance</td><td style="font-weight:bold; text-align:center; ">Sent</td><td style="font-weight:bold; text-align:center; ">Received</td><td style="font-weight:bold; text-align:center; ">Balance</td><td style="font-weight:bold; text-align:center; ">Remarks
If Any</td></tr><tr style="height:26px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td style="text-align:center; ">4</td><td style="text-align:center; ">May</td><td style="text-align:center; ">2011</td><td>Nawazan Bol.</td><td style="text-align:left; ">Samples</td><td style="text-align:center; ">Farm</td><td style="text-align:center; ">2212</td><td style="text-align:center; ">500</td><td style="text-align:center; ">10</td><td style="text-align:center; ">1722</td><td> </td></tr><tr style="height:26px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td style="text-align:center; ">10</td><td style="text-align:center; ">May</td><td style="text-align:center; ">2011</td><td style="text-align:center; ">Nawazan Bol.</td><td style="text-align:left; ">Samples</td><td style="text-align:center; ">General</td><td style="text-align:center; ">2212</td><td style="text-align:center; ">150</td><td style="text-align:center; ">0</td><td style="text-align:center; ">2062</td><td> </td></tr><tr style="height:26px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td>#N/A</td><td> </td><td> </td><td>#N/A</td><td> </td></tr><tr style="height:26px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td>#N/A</td><td> </td><td> </td><td>#N/A</td><td> </td></tr><tr style="height:26px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">6</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td>#N/A</td><td> </td><td> </td><td>#N/A</td><td> </td></tr></tbody></table>
<table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family:Arial; font-size:9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color:#cacaca; font-size:10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>G2</td><td>=VLOOKUP(D2,Sheet1!$A$2:$E$67,5,0)</td></tr><tr><td>J2</td><td>=SUM(G2-H2+I2)</td></tr><tr><td>G3</td><td>=VLOOKUP(D3,Sheet1!$A$2:$E$67,5,0)</td></tr><tr><td>J3</td><td>=SUM(G3-H3+I3)</td></tr><tr><td>G4</td><td>=VLOOKUP(D4,Sheet1!$A$2:$E$67,5,0)</td></tr><tr><td>J4</td><td>=SUM(G4-H4+I4)</td></tr><tr><td>G5</td><td>=VLOOKUP(D5,Sheet1!$A$2:$E$67,5,0)</td></tr><tr><td>J5</td><td>=SUM(G5-H5+I5)</td></tr><tr><td>G6</td><td>=VLOOKUP(D6,Sheet1!$A$2:$E$67,5,0)</td></tr><tr><td>J6</td><td>=SUM(G6-H6+I6)</td></tr></tbody></table></td></tr></tbody></table>

And in sheet3 I want summary of my stock. For example I want the summary of “Nawazan” for the month of “September”…


Sheet3

<table style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:62px;"><col style="width:87px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody><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></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td style="text-align:left; ">Name</td><td style="text-align:left; ">Nawazan</td><td><---</td><td>For Example I want summary of Nawazan</td><td> </td><td> </td><td> </td><td> </td><td> </td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td style="text-align:left; ">Year</td><td style="text-align:left; ">2011</td><td><---</td><td>Fro the Year 2011</td><td> </td><td> </td><td> </td><td> </td><td> </td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td style="text-align:left; ">Month</td><td style="text-align:left; ">September</td><td><---</td><td>For the month september</td><td> </td><td> </td><td> </td><td> </td><td> </td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td style="text-align:left; ">Date</td><td> </td><td><---</td><td>Optional</td><td> </td><td> </td><td> </td><td> </td><td> </td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr><tr style="height:34px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">6</td><td>Dated</td><td>Item Sent</td><td>Reference</td><td>Team</td><td>Opening
Balance</td><td>Sent</td><td>Received</td><td>Balance</td><td>Remarks
If Any</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">7</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">8</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">9</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr></tbody></table>


I am also having problems regarding Opening Balance and closing Balance that should be updated on the end of month.

Kindly help me.. give me advice how to create.. is it okay?? Or I want to create different sheet or fileds?? What formulaes should I apply or VBA.. please help making my stock ledger.
 
MrExcel Forums are Aw3some :mad: ... i've get my solution in one minute.. ohhhh it is so useful.. Woooow... Thanks all guruz.. thanks for Nothing..
 
Upvote 0

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
Back
Top