Print Selected Items on Excel Invoice

You have an Excel order form with a long list of products to select. After a customer enters quantities for the products they want to order, you want to print an invoice for the selected items.

Select the Products

Here’s the OrderForm sheet, where quantities have been entered for four of the products, in column E.

OrderFormQty01

Formulas in column B check the quantity column (E). If the quantity is filled in, then the next available number is calculated.

=IF(E6>0,MAX($B$5:B5)+1,””)

Print the Order

On the Invoice sheet, formulas pull the data from the Order form. For example, this formula shows the name of the first product that was ordered:

=IF(MAX(OrderForm!$B:$B)<$B12,””,
INDEX(OrderForm!$C$6:$C$12,MATCH($B12,OrderForm!$B$6:$B$12,0)))

Here’s the Order Form sheet, ready to be printed. The numbered rows from the Products sheet have been added to the Order form. Products without a quantity, such as Sweater, don’t appear.

OrderFormQty02

The Sample File

To download the sample file, please visit the Excel Sample Files page, on my Contextures website. In the Functions section, look for FN0027 – Print Marked Invoice Items.

___________________________

You may also like...

4 Responses

  1. Dan says:

    This was really useful . Can you tell me if there is a way to use this spreadsheet with products that have multiple sizes and ?
    Have is laid out so that the product sizes are at the right hand size of the row and each size is a quantity box

  2. Dan, in the order section, add a Total Qty column, then refer to that column in the Item # formula.

    Then, add the size columns to the printed invoice section, and use VLookup formulas to pull the Size data for each row.

  3. Dan says:

    Is there a way to set it up so that you can enter 2 or more sizes into on each row ? Then have each size product show up separately on the Printed invoice section

  4. Dan, it may be possible, and if you post your question in an Excel newsgroup, with more details, someone might be able to help you with the formulas, etc.

    You can access the newsgroups through the Microsoft web site.