Named Ranges

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

Named Grid Range properties

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

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
• 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
• Add a new named range with the following properties:
  1. Name: ProductMembers
  2. Scope: Workbook
  3. Members - Axis - Rows
• Insert a new Excel column chart, then right-click on it, and choose Select Data
• Add a Legend Entry (Series) as Series name: =Sheet1!CY2002Members; Series values: =Sheet1!CY2002Data
• Set the Horizontal category labels to =Sheet1!ProductMembers
• The chart will now update its members and data when the Grid changes

See Also

• Grid Properties
• Using named ranges to create dynamic charts in Excel