Named Ranges

From Version 7.6, XLCubed allows the automatic creation of named ranges based on parts of the Grid or Table. This simplifies many interactions between Excel and XLCubed, such as formulae or charts based on XLCubed ranges. The feature can be found on the Grid Properties or Table Properties form, on the Interaction tab. From here, you can see and edit existing ranges or set up new ones.

Named Range properties for Grids

Name: The name to give the named range; this is used to refer to the range in formulae and the chart dialogs
Scope: Where the named range is recognised - either all over the workbook, or tied to the Grid worksheet (for example, Sheet1!MyRange)
Whole Grid - Members and data: The named range covers all members and data in the entire Grid
Whole Grid - Data: The named range covers all the data in the Grid
Members - Axis: The named range covers the members on the specified axis
Members - Hierarchy: The named range covers the members belonging to the specified hierarchy
Slice - Members and data: The named range covers the members and data belonging to the specified slice
Slice - Members: The named range covers the members belonging to the specified slice
Slice - Data: The named range covers the data belonging to the specified slice

Named Range properties for Tables

Name: The name to give the named range; this is used to refer to the range in formulae and the chart dialogs
Scope: Where the named range is recognised - either all over the workbook, or tied to the Table worksheet (for example, Sheet1!MyRange)
Table - Headers and data: The named range covers all headers and data in the entire Table
Table - Headers: The named range covers all headers in the entire Table
Table - Data: The named range covers all the data in the Table
Table - Last Data Cell: The named range follows the bottom/right data cell. Useful as part of a range specification (for example, A1:LastDataCell)
Columns - Headers and data: The named range covers the headers and data belonging to the specified column
Columns - Headers: The named range covers the headers belonging to the specified column
Columns - Data: The named range covers the data belonging to the specified column

Example: Calculating an average of all the data shown in the Grid

Add a new named range with the following properties:
1. Name: GridData
2. Scope: Workbook
3. Whole Grid - Data
The formula =Average(GridData) placed anywhere in the workbook will now calculate the average value of the Grid.

Example: Calculating a formula for each column in the Grid

Add a new named range with the following properties:
1. Name: GridData
2. Scope: Workbook
3. Whole Grid - Data
The formula =SUM(GridData B:B) will give the Sum of column B. You can fill the formula across to compute other columns

Example: Charting all the data shown for Calendar Year 2002 in the Grid

Based on the Adventure Works demo cube

Create a Grid with the years across columns, and Products on rows & then add three named ranges. Named ranges are added through the Grid Properties,Interaction tab:

Add a new named range with the following properties:
1. Name: CY2002Members
2. Scope: Workbook
3. Slice - Members, then add Date Calendar - CY 2002 to the slice

Add a new named range with the following properties:
1. Name: CY2002Data
2. Scope: Workbook
3. Slice - Data, then add Date Calendar - CY 2002 to the slice