Excel Document Interface
Available in NLS 5.12.1 and earlier
NLS 5.13 and laterBeginning with NLS 5.13, importing and viewing of Excel reports is no longer supported.
A Microsoft Excel spreadsheet can be added to the list of NLS reports under either the Contact or Loan sections. These documents are created using a copy of the Excel spreadsheet
Excel Report Info and List of NLS Fields.xls and customizing Sheet1 to utilize the data NLS creates on Sheet2 at the time the individual report is run.
Excel Report Info and List of NLS Fields.xls file can be downloaded from the Nortridge Software Community in the API, Scripting and Developer Questions category. The file is included in the Office Integration.zip file located in the Office Integration Files topic.
Excel reports can only be run individually and can be added to the Contact or Loan Correspondence for later review.
Step by Step
Step 1: Create the Spreadsheet
- Make a copy of
Excel Report Info and List of NLS Fields.xlsand name it
Step 2: Make a Reference Page
- Click on the tab NLSDATA.
- To provide a handy reference, print this sheet (it is already formatted to print the row and column headers). This will give you a list of all the NLS fields that will be entered into Column B when the report is run.
NoteThe range NLSDATA!A1:B247 is named NLS. You can use the name with the
VLOOKUPfunction to easily insert NLS generated data onto Sheet1.
Step 3: Add Your Customization
- On Sheet1, enter your customizations. You may add a logo, create an invoice, or make any other type of Excel report that will pull data from the NLS database. When you want to grab NLS data, use the
VLOOKUPfunction as follows:
=VLOOKUP(“name in column A”,NLS,2,FALSE)
ExampleTo get the Loan Number into cell A1 on Sheet1, enter the function:
(This is equivalent to entering =NLSDATA!B122, but it is easier to debug).
The parameter in quotes must be exactly as shown in Column A on the sheet NLSDATA. Column A is overwritten by NLS each time the report is generated, so you cannot change the names. NLS only writes in columns A and B on sheet NLSDATA. You can customize all other columns on the sheet NLSDATA and they will not be disturbed.
There are currently 120 Contact fields to chose from and an additional 127 Loan fields. If the report is a contact report, only the contact information will be filled in by NLS. If the report is a loan report, all the fields will be filled in.
You can also reference the data in NLSDATA directly by cell reference.
VLOOKUPmakes this easier. Refer to Excel Help for more information on
VLOOKUPand Named Ranges.
- Save the document. Be sure your cursor is in cell Sheet1!A1. This way, when you preview your report in NLS you will see the upper left corner of your report. It is a feature of Excel that all spreadsheets automatically open to the place where the cursor was last left.
Step 4: Install the Document into NLS
- Select Setup from the NLS Main slider.Click File > Setup.
- Click System.
- Click Report Manager.
- Click Add Report.
- Fill in the following fields:
Type: Select CIF or Loan.
Category: Enter an existing category or create a new one. For example, you might want all of your spreadsheets to be in a category called Spreadsheets.
Format Title: Enter a title as you want it to appear in the Report tree (e.g. LOAN BALANCE SPREADSHEET LETTER).
Path: Use the picker. If this is a contact report, select the contact folder. If it is a loan report, select the loan folder.
Format File: Click to locate and select your Excel file.
- Click OK.
- Click OK again to exit system setup.
- Click Yes to restart NLS so that you can access your new Excel report.
Running an Excel Report
Excel reports are individual reports only. They are accessed from the Contact or Loan query screens. Click