How to use LOOKUP functions to auto-complete Invoice

[aglawrence]

Hi everyone,

I am trying to create an invoice template in Excel which will automatically populate with the products which have been ordered, along with their price and quantity.

I have a single worksheet which contains over 5,000 rows as follows:

Qty	Description	Net Price	Tax	Gross Price
	Pump	10.00	2.00	12.00
1	Motor	5.00	1.00	6.00
	Seal	20.00	4.00	24.00
	Panel	1.00	0.20	1.20
4	Trim	2.00	0.40	2.40
	Rotor	5.00	1.00	6.00

<tbody>
</tbody>

When someone orders an item, we enter a number into the QTY column, as above. The desired result in the Invoice Template worksheet would be as follows:

Qty	Description	Net Price	Tax	Gross Price
1	Motor	5.00	1.00	6.00
4	Trim	2.00	0.40	2.40

<tbody>
</tbody>

Both worksheets would be contained in the same workbook.

Does anyone know how we can achieve this?

Thanks and regards,

Andy

 

[JoeMo]

Welcome to the forum!

Here's a macro approach for you to consider. First set up a sheet with tab name "All" (w/o the quote marks). This sheet will show all the products as in the first layout in your post. The header "Qty" will be in cell A1. Then set up a second sheet with the tab name "On Order" (w/o the quote marks), and copy the headers from sheet "All" to it starting in cell A1.

The code below is change event code for the sheet "All". The code will automatically trigger, anytime a change is made to a cell in the Qty column on sheet "All". and the "On Order" sheet will be updated accordingly. Below are instructions for installing the code, and the code itself.
To install sheet code:
1. Right-click the worksheet tab you want to apply it to and choose 'View Code'. This will open the VBE window.
2. Copy the code below from your browser window and paste it into the white space in the VBE window.
3. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
4. Make sure you have enabled macros whenever you open the file or the code will not run.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
If Not Intersect(Target, Columns("A")) Is Nothing Then
    Sheets("On Order").Range("A1").CurrentRegion.Offset(1).ClearContents
    On Error Resume Next
    For Each c In Range("A:A").SpecialCells(xlCellTypeConstants, xlNumbers)
        c.Resize(1, 5).Copy Sheets("On Order").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    Next c
    On Error GoTo 0
End If
End Sub

 

[aglawrence]

Thanks Joe, that works perfectly on my PC. However, the macro does not work on my Android smartphone, where I sometimes need to create invoices.

Can anyone suggest a non-macro way of doing this?

Andy

 

[Mike Szczesny]

Can you uses this. You need to use Cntrl +Shift+ Enter Copy down and across.

=IFERROR(INDEX(A$2:A$7,SMALL(IF(A$2:A$7<>"",ROW(A$2:A$7)-ROW(A$2)+1),ROWS($A$10:A10)))," ")

<tbody>
</tbody>

Qty	Description	Net Price	Tax	Gross Price
	Pump	10	2	12
1	Motor	5	1	6
	Seal	20	4	24
	Panel	1	0.2	1.2
4	Trim	2	0.4	2.4
	Rotor	5	1	6
Qty	Description	Net Price	Tax	Gross Price
1	Pump	10	2	12
4	Motor	5	1	6

<colgroup><col><col><col span="2"><col></colgroup><tbody>
</tbody>

 

[Mike Szczesny]

Please disregard previous entry. The reply function did not allow me to correct my entry.

=IFERROR(INDEX($A$2:$A$7,SMALL(IF($A$2:$A$7<>"",ROW($A$2:$A$7)-ROW(A$2)+1),ROWS($A$10:A10)))," ") Use Cntrl+Shift+Enter =INDEX(B$2:B$7,MATCH($A$10,$A$2:$A$7,0)) Copy across <tbody> </tbody>

<tbody>
</tbody>

 

[aglawrence]

Thanks Mike, that worked.

Andy