6.2.3 Microsoft Excel - Named Range Adaptor
The Excel – Named Range Adaptor is useful for retrieving a value in a cell that has been named. This adaptor is safer to use than the Excel – Cell Address adaptor as the name of a cell is not impacted by the insertion or deletion of rows and columns, or the renaming of worksheets.
Named Range Scope
A named range can be either workbook scoped, or scoped to a particular worksheet. DPM Authority can retrieve the value of any named range as long as the named range resolves and refers to a single cell, rather than a range of cells.
Workbook scoped
Workbook scoped named ranges are the most frequently used named ranges. All named ranges created using the Excel Name Box are workbook scoped.
A workbook scoped named range can refer to any cell or range in any sheet. If the sheet to which the named range is referring to is deleted, the named range persists but does not resolve. Names must be unique, regardless of which sheet the named ranges are referring to.
Worksheet scoped
A named range can be scoped to a particular worksheet. Worksheet scoped named ranges can be created using Excel’s Name Manager (Formulas -> Name Manager):
A worksheet scoped named range can refer to any cell or range in any sheet. Named ranges scoped to a particular worksheet are deleted if that particular worksheet is deleted. Names must be unique within a particular worksheet, but duplicate names can be used if scoped to a different worksheet. This can allow for adding named ranges based on COREP/FINREP row and column references (e.g. “_r010c010”) even though there will be many with the same name on different worksheets.
Automation Panel
DPM Authority will list all the range names found in the workbook referenced by the connection in the Automation Panel.
Worksheet scoped named ranges are listed beneath their scoped worksheet. See “_r010c001” listed beneath both sheets “C 00.01” & “C 01.00” (duplicate names are allowed as long as they are scoped to a different worksheet). A named range can be dragged from the Automation panel and dropped on the report. If dropped onto an existing data object, its adaptor will change to reference that named range.
Changes to Named Ranges
DPM Authority caches both the list of named ranges as well as the current value of each named range. To speed up automation, the cached values will be used when dragging and dropping named ranges so that the spreadsheet does not have to be opened with each drag and drop action. If either the list of named ranges or their values have changed since the values have been cached, they can be updated by pressing the Update Automation Panel button at the top of the Automation panel:
Value Editor
An Excel – Named Range adaptor can be assigned or changed using the Adaptor control in the value panel:
The adaptor can be edited by clicking on the Configure Adaptor button:
Excel Named Range Data Adaptor Form:
Range Name
A name can be split into two parts (Part1 & Part2). DPM Authority searches for a named range determined by the concatenation of part 1 & 2.
DPM Authority will list those named ranges it finds in the workbook defined by the connection under the “Read In Values” section of the combo box:
Depending on the values returned by the cells, named ranges may be text, date, or decimal, as shown below
TIPS:
You can apply Excel data adaptors to the current selection with Ctrl + T. Use the context menu to select an entire table / row / column, then press Ctrl + T to apply data adaptors to all selected cells.
Drag and drop Excel automation nodes onto the table’s row/column headers to apply them to the entire row or column.