Invoice and Packing List on Same Form

A shipping packing list is also called a delivery docket. It shows what goods are included in the shipment and also serves as a bill of lading. It is naturally related to invoices. This template shows how to create a packing list and an invoice within the same form.

The packing slip template (cn010) shows you how to create a standalone template. You can easily retrieve and print a saved invoice using the packing list template. If you always print both documents at the same time, then consider this template.

In this sample, we will use c4074 as the base template and add a packing list to the same form so that when an invoice is printed, a corresponding packing list will be printed too.

To achieve this goal, we first have to copy the existing form design to create a new form, and then modify the new form to make it suitable for packing lists. And finally, we modify the definition of the printable form to have Excel print both documents at the same time.

Let's see the detailed procedures and steps involved in this customization.

1. First, backup the template that you want to customize. Baking up data frequently saves you a lot of time and effort when the newest version of data is lost.
2. Open the template.
3. Switch to design mode by clicking the "Design Mode" button on the worksheet.
4. Pull down the name box located on the left end of the formula bar. On the end of the name list you'll find " Print_Area". Click to select it.
5. Excel selects all the cells defined by the "Print_Area" name. This is the printable area that is printed when you print the current worksheet.
6. On the Excel "Home" ribbon tab, click "Copy" in the "Clipboard" group. This copies the entire printable form to the clipboard.
7. Choose an empty area underneath the existing printable form. In this sample, our original printable area is defined at D2:L47, so we click and select D50. This will be our starting cell of the new packing list form.
8. On Excel "Home" ribbon tab, pull down "Paste" in the "Clipboard" group and then choose "Paste All".
9. Now the key step: pull down "Paste" in the "Clipboard" command group again and choose "Paste Link" in the "Other Paste Option" section. This will create a simple formula for each cell in the paste destination section. The simple formula retrieves data from the original printable form; fill it in the packing list section.
10. Till now, our new packing list form has exactly the same format and content as the invoice. Modify it to meet your requirements. For example, change the title from "Tax Invoice" to "Delivery Docket".
11. In this sample, we also clear the "Price" and "Amount" columns, and then expand the "Description" column.
12. We also cleared the bottom section that details "Freight", "GST", "Total Incl. GST", etc.
13. Add your notes and declarations to the form, usually at the bottom of the form.
14. Now once again, select "Print_Area" from the name box on the left end of the formula bar. This selects the original printable area.
15. Now push down the CTRL key on the keyboard and drag the mouse to select the new packing list area. In this sample, it is D50:L92.
16. Release the keyboard CTRL key.
17. With both the invoice and packing list forms selected, from the Excel "Page Layout" ribbon, choose "Print Area", and then "Set Print Area". This defines the new printable form. As you can see, the print area could be a discrete area that contains multiple ranges.
18. Go to the Excel menu File / Print Preview. Make sure the worksheet is printed on two pages.
19. Exit design mode once you have done the customization.
20. Save the template.

Now, when you click Print, it will print two documents: one invoice and one packing list.

To overcome the drawback of having to always print the two documents at the same time, place the packing list on another worksheet. This way, the packing list worksheet can have its own "Print_Area" definition and thus be printed separately.