Automatically read conditional data from one sheet and write conditionally on other

N

niting85

New Member
Joined
Mar 26, 2016
Messages
1
I am trying to make an Invoice and inventory management sheet in excel.

Sheet1(ITEMS) - List of all items, Name, Value, tax, Unit of Measurement
[TABLE="width: 499"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]S.No[/TD]
[TD]ITEM NAME[/TD]
[TD]ITEM CODE[/TD]
[TD]UNIT OF MEASURMENT[/TD]
[TD]SALE PRICE[/TD]
[TD]VAT RATE[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]COPIER/PAPER[/TD]
[TD="align: right"]1001[/TD]
[TD]REAM[/TD]
[TD="align: right"]140[/TD]
[TD="align: right"]5.00%[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]ECO PEN1/BALL POINT PEN[/TD]
[TD="align: right"]1002[/TD]
[TD]pcs[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0%[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]ECO PEN2[/TD]
[TD="align: right"]1003[/TD]
[TD]pcs[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0%[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]PHOTO FRAME1[/TD]
[TD="align: right"]1004[/TD]
[TD]pcs[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]12.50%[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]PHOTO FRAME2[/TD]
[TD="align: right"]1005[/TD]
[TD]pcs[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]12.50%[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]SLIP PAD[/TD]
[TD="align: right"]1006[/TD]
[TD]pcs[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5.00%[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]PEN STAND[/TD]
[TD="align: right"]1007[/TD]
[TD]pcs[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]12.50%[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]SLIPBOX+PENSTAND COMBO[/TD]
[TD="align: right"]1008[/TD]
[TD]pcs[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]12.50%[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]SLIPBOX[/TD]
[TD="align: right"]1009[/TD]
[TD]pcs[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]12.50%[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]NOTEBOOK WITH POSTIT & PEN[/TD]
[TD="align: right"]1010[/TD]
[TD]pcs[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]12.50%[/TD]
[/TR]
</tbody>[/TABLE]
Sheet2(SALES INVOICE) - Invoice Template, we select items and input qty to print invoice.
[TABLE="width: 697"]
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 9"]TAX INVOICE[/TD]
[/TR]
[TR]
[TD="colspan: 2"] [/TD]
[TD="colspan: 5"] [/TD]
[TD]Invoice No :[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Date : [/TD]
[TD]26-03-2016[/TD]
[/TR]
[TR]
[TD="colspan: 2"] [/TD]
[/TR]
[TR]
[TD="colspan: 7"] [/TD]
[TD="colspan: 2"] [/TD]
[/TR]
[TR]
[TD="colspan: 7"] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="colspan: 7"] [/TD]
[TD="colspan: 2"] [/TD]
[/TR]
[TR]
[TD="colspan: 7"]CIN: U74140DL2010PTC203115[/TD]
[TD="colspan: 2"] [/TD]
[/TR]
[TR]
[TD="colspan: 7"]INVOICE TO[/TD]
[TD="colspan: 2"] [/TD]
[/TR]
[TR]
[TD="colspan: 7"] [/TD]
[TD="colspan: 2"] Work Order No - NA[/TD]
[/TR]
[TR]
[TD="colspan: 7"] [/TD]
[TD="colspan: 2"]ORIGINAL[/TD]
[/TR]
[TR]
[TD="colspan: 7"] [/TD]
[TD="colspan: 2"]DUPLICATE[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Sr.No[/TD]
[TD]ITEM NAME[/TD]
[TD]ITEM CODE[/TD]
[TD]UOM[/TD]
[TD]VAT RATE[/TD]
[TD]UNIT PRICE(Rs.)[/TD]
[TD]QTY[/TD]
[TD="colspan: 2"]TOTAL PRICE (Rs.)[/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]ECO PEN1/BALL POINT PEN[/TD]
[TD="align: right"]1002[/TD]
[TD]pcs[/TD]
[TD]0%[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]10[/TD]
[TD="colspan: 2"]₹ 50.00[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD="colspan: 2"] [/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD="colspan: 2"] [/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD="colspan: 2"] [/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD="colspan: 2"] [/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD="colspan: 2"] [/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD="colspan: 2"] [/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD="colspan: 2"] [/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD="colspan: 2"] [/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD="colspan: 2"] [/TD]
[/TR]
[TR]
[TD="colspan: 7"]TOTAL[/TD]
[TD="colspan: 2"]₹ 50.00[/TD]
[/TR]
[TR]
[TD="colspan: 7"]VAT+SURCHARGE @ 0%[/TD]
[TD="colspan: 2"]₹ 0.00[/TD]
[/TR]
[TR]
[TD="colspan: 7"]VAT+SURCHARGE @ 5.25%[/TD]
[TD="colspan: 2"]₹ 0.05[/TD]
[/TR]
[TR]
[TD="colspan: 7"]VAT+SURCHARGE @ 13.125%[/TD]
[TD="colspan: 2"]₹ 0.13[/TD]
[/TR]
[TR]
[TD="colspan: 7"]GRAND TOTAL[/TD]
[TD="colspan: 2"]₹ 51.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Rs. (In Words). [/TD]
[TD="colspan: 8"]Rupees FiftyOne Only [/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD="colspan: 7"]1. Make all cheques payable to GREENOBIN RECYCLING PRIVATE LIMITED[/TD]
[TD="colspan: 2"] [/TD]
[/TR]
[TR]
[TD="colspan: 7"]2. If you have any questions concerning this invoice please contact us @ Your company number or email us at Your company email id[/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD="colspan: 7"]3. Goods Return Within 7 Days[/TD]
[/TR]
[TR]
[TD="colspan: 7"]THANK YOU FOR YOUR BUSINESS[/TD]
[/TR]
</tbody>[/TABLE]
Sheet3 (STOCK oUT) - On a button press on sheet2, qty for items which are invoiced shall be reflected to the respective column in Sheet3 in a new row on every invoice.

Need help to get VB code for writing macro.

Any help is highly appreciated.
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
You must log in or register to reply here.

Similar threads

O
  • Question Question
Too much condition to pull information invoice from multiple sheets
Replies
0
Views
290
Omar M
O
A
  • Solved
put the QTY to adjacent ID based on arranged sheet when compare two sheets
Replies
7
Views
261
abdo meghari
A
H
  • Question Question
populate average price on listbox based on selected sheet from combobox
Replies
1
Views
313
RoryA
RoryA
K
  • Solved
Populate summary by sorting data for customer for each sheet alone based on dates
Replies
4
Views
168
KalilMe
K
K
  • Question Question
populate amounts for column debit,credit for each customer from file to another
Replies
0
Views
333
KalilMe
K

Forum statistics

Threads
1,223,275
Messages
6,171,119
Members
452,381
Latest member
Nova88

Share this page

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