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: 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:
NR10.png
  • 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
NR2.png
  • 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
NR3.png
  • Add a new named range with the following properties:
    1. Name: ProductMembers
    2. Scope: Workbook
    3. Members - Axis - Rows
NR4.png
  • You should end up with a list like this:
NR5.png
  • Insert a new Excel column chart, then right-click on it, and choose Select Data - you will see this window:
NR11.png
  • Add a Legend Entry (Series) as Series name: =Sheet1!CY2002Members; Series values: =Sheet1!CY2002Data
NR6.png
  • Edit the Horizontal category labels to =Sheet1!ProductMembers and click OK
NR12.png
NR7.png
  • The chart looks like this:
NR8.png
  • The chart will now update its members and data when the Grid changes
NR9.png

See Also