You are here:

Excel/Generate previous invoice based on invoice number

Advertisement


Question
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

Answer
Hello Sofia,

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
     With Worksheets("Register")
        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
     End With
  End If
  Exit Sub
NotFound:
  MsgBox "Invoice " & Target.Value & " not found.", vbInformation, "Register Search"
End Sub
______________________

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.

Damon
About Excel
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

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Damon Ostrander

Expertise

I have extensive experience with VBA programming in Excel 5 through Excel 2013. As a former aerospace engineer with a large aerospace corporation and consultant in a small defense technology services company, I have developed a wide range of applications in VBA, including simulations involving mixed-language programming, satellite orbit mechanics, graphics and animation, and real-time applications. I am interested in moderate to hard VBA-related questions only.

Experience

I have developed and taught several courses in Excel VBA programming and also VBA programming in Office 97, 2000, and 2007. I have developed a number of large technical applications in Excel VBA for use within the aerospace industry.

Education/Credentials
B.S. in Electrical Engineering and Computer Science, University of California, Berkeley.

©2016 About.com. All rights reserved.