Difference between revisions of "Named Ranges"

(→‎Example: Charting all the data shown for Calendar Year 2002 in the Grid)
 
(12 intermediate revisions by 4 users not shown)
Line 1: Line 1:
−
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 {{Menu|Interaction}} tab. From here, you can see and edit existing ranges or set up new ones.
+
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 {{Menu|Interaction}} tab. From here, you can see and edit existing ranges or set up new ones.
  
 
==Named Range properties for Grids==
 
==Named Range properties for Grids==
Line 18: Line 18:
 
;Table - Headers :The named range covers all headers 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 - 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, {{Code|A1:LastDataCell}})
 
;Columns - Headers and data :The named range covers the headers and data belonging to the specified column
 
;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 - Headers :The named range covers the headers belonging to the specified column
Line 29: Line 30:
 
* The formula {{Code|1==Average(GridData)}} placed anywhere in the workbook will now calculate the average value of the Grid.
 
* The formula {{Code|1==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==
+
==Example: Calculating a formula for each column in the Grid==
 +
* Add a new named range with the following properties:
 +
*# Name: GridData
 +
*# Scope: Workbook
 +
*# Whole Grid - Data
 +
* The formula {{Code|1==SUM(GridData B:B)}} will give the Sum of column B. You can fill the formula across to compute other columns
 +
 
 +
[[Image:GridNamedRangeTotal.png|350px|centre]]
 +
 
 +
==Example: Charting all the data shown for Calendar Year 2012 in the Grid==
 
'''Based on the Adventure Works demo cube'''
 
'''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:
 
* 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:
−
[[Image:NR10.png|350px|centre]]  
+
[[Image:NR10.png|450px|centre]]  
  
 
* Add a new named range with the following properties:
 
* Add a new named range with the following properties:
−
*# Name: CY2002Members
+
*# Name: CY2012Members
 
*# Scope: Workbook
 
*# Scope: Workbook
−
*# Slice - Members, then add Date Calendar - CY 2002 to the slice
+
*# Slice - Members, then add Date Calendar - CY 2012 to the slice
−
[[Image:NR2.png|350px|centre]]
+
[[Image:NR2.png|450px|centre]]
 
* Add a new named range with the following properties:
 
* Add a new named range with the following properties:
−
*# Name: CY2002Data
+
*# Name: CY2012Data
 
*# Scope: Workbook
 
*# Scope: Workbook
−
*# Slice - Data, then add Date Calendar - CY 2002 to the slice
+
*# Slice - Data, then add Date Calendar - CY 2012 to the slice
−
[[Image:NR3.png|350px|centre]]
+
[[Image:NR3.png|450px|centre]]
 
* Add a new named range with the following properties:
 
* Add a new named range with the following properties:
 
*# Name: ProductMembers
 
*# Name: ProductMembers
 
*# Scope: Workbook
 
*# Scope: Workbook
 
*# Members - Axis - Rows
 
*# Members - Axis - Rows
−
[[Image:NR4.png|350px|centre]]
+
[[Image:NR4.png|450px|centre]]
 
* You should end up with a list like this:
 
* You should end up with a list like this:
−
[[Image:NR5.png|350px|centre]]
+
[[Image:NR5.png|450px|centre]]
 
* Insert a new Excel column chart, then right-click on it, and choose {{Code|Select Data}} - you will see this window:
 
* Insert a new Excel column chart, then right-click on it, and choose {{Code|Select Data}} - you will see this window:
 
[[Image:NR11.png|350px|centre]]
 
[[Image:NR11.png|350px|centre]]
−
* Add a {{Code|Legend Entry (Series)}} as Series name: {{Code|1==Sheet1!CY2002Members}}; Series values: {{Code|1==Sheet1!CY2002Data}}  
+
* Add a {{Code|Legend Entry (Series)}} as Series name: {{Code|1==Sheet1!CY2012Members}}; Series values: {{Code|1==Sheet1!CY2012Data}}  
 
[[Image:NR6.png|350px|centre]]
 
[[Image:NR6.png|350px|centre]]
−
 
+
* Edit the {{Code|Horizontal category labels}} to {{Code|1==Sheet1!ProductMembers}} and click OK
 
[[Image:NR12.png|350px|centre]]
 
[[Image:NR12.png|350px|centre]]
−
* Edit the {{Code|Horizontal category labels}} to {{Code|1==Sheet1!ProductMembers}} and click OK
 
 
[[Image:NR7.png|350px|centre]]
 
[[Image:NR7.png|350px|centre]]
 
* The chart looks like this:
 
* The chart looks like this:
−
[[Image:NR8.png|350px|centre]]
+
[[Image:NR8.png|450px|centre]]
 
* The chart will now update its members and data when the Grid changes
 
* The chart will now update its members and data when the Grid changes
−
[[Image:NR9.png|350px|centre]]
+
[[Image:NR9.png|450px|centre]]
  
 
==See Also==
 
==See Also==

Latest revision as of 15:40, 18 December 2023

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
GridNamedRangeTotal.png

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:
NR10.png
  • 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
NR2.png
  • 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
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!CY2012Members; Series values: =Sheet1!CY2012Data
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

Retrieved from ‘https://help.fluencexl.com/index.php?title=Named_Ranges&oldid=12265’