Excel/Generate previous invoice based on invoice number
I have a sheet: register in which all details of my previous invoices are saved. Each row contains information of one single invoice. I want to generate previous invoice based on invoice numbers. However in other sheet:Inv I have an invoice format and details of each row from the register sheet will go to different cells based on the invoice number I put in the invoice number box.
Invoice number cell: H13 (sheet:inv)
Register sheet contains: A5 - customer name, B5 - date, C5 - invoice number, D5 - product and so on
Invoice sheet contains: B13 - customer name, H15 - date, invoice number - H13, B22 - product.
Can you please help with this one. Thanks in advance
If I understand your question correctly, this code should do what you want.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$H$13" Then
Dim iRow As Long
On Error GoTo NotFound
'find Register sheet row with desired invoice number
iRow = WorksheetFunction.Match(Range("H13"), .Range("C2:C1000"), 0) + 1
Range("B13") = .Cells(iRow, "A") 'Customer name
Range("H15") = .Cells(iRow, "B") 'Date
Range("B22") = .Cells(iRow, "D") 'Product number
MsgBox "Invoice " & Target.Value & " not found.", vbInformation, "Register Search"
This code should be placed in the Inv sheet's code module. To do this right-click on the Inv sheet tab and select View Code. Then paste this code into the Code pane that appears. You should then be able to go back to the Inv sheet and type the desired invoice number into H13 and the appropriate cells will be populated.
Feel free to follow up if I have failed to interpret your question correctly.
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here