You must understand named ranges before you use source data from Microsoft Excel spreadsheets, whether in crosstab or database format.
To use a crosstab as a source, you must define one or more named ranges in the spreadsheet. These ranges establish which data becomes columns in the model.
To use a database as a source, you must define a named database range in the spreadsheet, and also specify that range in Transformer. Transformer reads the named ranges from the crosstab or database, and the data class (text, numeric, or date) for each cell value.
If the Data Sources list contains columns identifying named ranges that you do not need, delete them from the list. Deleting columns in Transformer does not affect your spreadsheet.
Before further processing, Transformer verifies that the named range represented by each column in the model still exists in the spreadsheet and that the spreadsheet still contains the same number of pages, rows, and columns as it did when you first identified it as a data source for the model. If differences exist, you are prompted to use the Modify Columns command on the Tools menu to add, modify, match, or remove columns as required, before proceeding.
For more information about how to define named ranges, see the documentation provided with your spreadsheet software.
This example shows one page of a multipage crosstab.
| A | B | C | D | E | |
| 1 | Glassware Division | Q1 | Q2 | Q3 | Q4 |
| 2 | |||||
| 3 | Income-Net Sales | 10,000 | 10,000 | 15,000 | 20,000 |
| 4 | Expenses-Salary | 2,000 | 2,000 | 2,500 | 2,500 |
| 5 | Expenses-Rent | 800 | 800 | 800 | 800 |
| 6 | Expenses-Advertising | 600 | 700 | 600 | 600 |
| 7 | Expenses-Production | 1,500 | 1,500 | 2,000 | 4,000 |
| 8 | Net Income | 5,100 | 5,000 | 9,100 | 12,000 |
For this crosstab, the following ranges were named, and these range names automatically become the columns in the model.
| Name | Range | Name | Range |
| Division | A1 | Expense | A4 .. A7 |
| Quarter | B1 .. E1 | Expense Amount | B4 .. E7 |
| Income | B3 .. E3 | Net Income | B8 .. E8 |
For a model requiring only the Division, Quarter, Expense, and Expense Amount columns, Transformer reads all the expense types and expense amounts for each quarter and for each division.
| Division | Quarter | Expense | Expense Amount |
| Glassware | Q1 | Expenses-Salary | 2,000 |
| Glassware | Q2 | Expenses-Salary | 2,000 |
| Glassware | Q3 | Expenses-Salary | 2,500 |
| Glassware | Q4 | Expenses-Salary | 2,000 |
| Glassware | Q1 | Production | 1,500 |
| Glassware | Q3 | Production | 1,500 |
In your Transformer model, under Glassware, the Data Sources list shows the columns Expense Amount, Expense, Income, Net Income, and Quarter.
You have a simple spreadsheet database that has a single named range.
| A | B | C | D | |
| 1 | EMP_NO | NAME | DEPT | SALARY |
| 2 | 0256 | Wilson | TECHW | 50,000 |
| 3 | 0141 | Barnes | DESIGN | 60,000 |
| 4 | 0724 | Paul | DESIGN | 70,000 |
| 5 | 1290 | Power | DESIGN | 80,000 |
The named range for cells A1 .. D5 is DataTable.
Transformer reads rows 1 to 5 in the range DataTable. The Data Sources list shows the Human Resources columns EMP_NO, NAME, DEPT, and SALARY.