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.
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.
... i've get my solution in one minute.. ohhhh it is so useful.. Woooow... Thanks all guruz.. thanks for Nothing..
