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.
Formula Adds Numbers
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)))
Print the Order Form
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.
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.
___________________________