Screenshots and Images
The printed version.
Open the template in Excel.
You are safe to download the resources. We've added our digital signatures to the files.
Download this if you have already installed the Microsoft Store edition of Invoice Manager for Excel, and are looking for additional customized templates.
Download this if you want this design to be set as the default template by the installer program.
Product Names and Shipping Address on Aging Report
Product Info on Accounts Receivable Aging Report
Related template fixed: c4055 c4059 c5084 c4103
Related gallery fixed: office,5
The "Aging" report in our Invoice Manager program is also called Account Receivable Aging Report. In earlier versions of Excel Invoice Manager, it is also called Customer Statement. This sample for bank analyst shows how to add item names from invoice detail section to the aging report.
The account receivable aging report is based on the "Invoice Header" database table. This is where the "Shipping Address" field (cell name "oknShipAddress") saved to. So there is no problem to display the shipping address on the aging report. Here are the brief steps to follow:
- Activate the "Aging" report worksheet if it isn't the current one.
- Go to Excel "View" ribbon tab and push check the "Gridlines" and "Headings" boxes in the "Show" group.
- Click to select the first heading cell on the aging report. Usually it is "invoice#" (the cell name is "oknAgInvoiceID".
- Click "Format Painter" on Excel "Home" ribbon tab.
- Click the first empty cell on the heading line -- in this case, it is $O$24. This copies the format of the first heading cell to the empty cell.
- Name this cell "oknAgShipAddress". This tells Invoice Manager that the column should fill with the shipping address information.
Filling product names into the aging report is a bit complex. This is because the product names -- i.e. the "Description" column on the invoice worksheet -- is saved to the "Invoice Body" database table. The relationship between the "invoice body" and "Invoice Header" is a one-to-many relationship. So it is necessary to combine all the product names into one, and then save the combined text to the "Invoice Header" database table. See Office Invoice Templates for more samples on this.
It is not as easy as you might imagine to combine several cell values into one. As explained on trumpexcel.com there are multiple ways you can do this, but Excel offers no special formula for this -- except Excel 2016 (Office 365), which offers the "TEXTJOIN" function.
Since our templates should support earlier version of Excel, we use the old "CONCATENATE" function. The following formula looks a bit long but it works for all the major Excel versions:
=TRIM(CONCATENATE(oknProductName_1," ",oknProductName_2," ",oknProductName_3," ",oknProductName_4," ",oknProductName_5," ",oknProductName_6," ",oknProductName_7," ",oknProductName_8," ",oknProductName_9," ",oknProductName_10," ",oknProductName_11," ",oknProductName_12))
This formula combines all the product description cells and separates them with a space. The "TRIM" function is used for removing all the redundant spaces from the result.
In this sample, the result of the formula is assigned to the "oknStatementNotes" cell. We then define a custom field "StatementNotes" in the "Invoice Header" database table.
Now it is easy to add the "StatementNotes" field from the "Invoice Header" database table. Just follow the steps described earlier for the "Shipping Address" field. Download the sample invoice template for free to find out how it works.
Format and Specification
|Name||Product Names and Shipping Address on Aging Report|
|Category||Service Invoice Template|
|Release Date||Tuesday, November 13, 2018|
|Format (XLS or XLSX)||.xlsx|
|Line Height (Points)||16.50|
|Papaer Size / Orientation||Portrait|
|Default Margins (Points)|
|Price||Free (0.00 USD)|
|System||Windows 7 and later, and Excel 2007 and later.|