Named Ranges

From Version 7.6, FluenceXL allows the automatic creation of named ranges based on parts of the Grid or Table. This simplifies many interactions between Excel and FluenceXL, such as formulae or charts based on FluenceXL 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 2012 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: CY2012Members
  2. Scope: Workbook
  3. Slice - Members, then add Date Calendar - CY 2012 to the slice

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

• Add a new named range with the following properties:
  1. Name: ProductMembers
  2. Scope: Workbook
  3. Members - Axis - Rows

• You should end up with a list like this:

• Insert a new Excel column chart, then right-click on it, and choose Select Data - you will see this window:

• Add a Legend Entry (Series) as Series name: =Sheet1!CY2012Members; Series values: =Sheet1!CY2012Data

• Edit the Horizontal category labels to =Sheet1!ProductMembers and click OK

• The chart looks like this:

• The chart will now update its members and data when the Grid changes

See Also

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