Difference between revisions of "XL3Link"

(Added categories)
(Toolbar buttons)
 
(25 intermediate revisions by 6 users not shown)
Line 1: Line 1:
[[File:XL3Link.png]]
+
The XL3Link() formula helps users easily build linked analysis into their reporting, as described in the diagram below from Stephen Few's book on Information Dashboard Design.
 +
 
 +
[[File:Linktodetail.png]]
 +
 
 +
The formula has two main functions. Firstly it jumps to another location in the workbook, like a hyperlink, and secondly it passes parameters held in one cell(s) into another cell(s).When you follow the hyperlink, XLCubed jumps to the sheet stored at LinkLocation and sets the parameters at the specified parameter addresses. Since selections for XLCubed Grids and formulae can be based on the content of a cell, they can update to reflect the passed parameter immediately.
 +
 
 +
Use the {{Menu|Insert Formula|XL3Link}} menu or ribbon item to insert the formula using a wizard. More details can be found [[Insert Link|here]].
 +
 
 +
==Syntax==
 +
 
 +
{{Code|XL3Link( [LinkLocation], [FriendlyName], [LinkType], [Range1], [Value1],…, [Range13], [Value13] )}}
 +
 
 +
==Parameters==
 +
{| class="wikitable"
 +
! Parameter
 +
! Description
 +
|-
 +
| {{Code|LinkLocation}}
 +
| A piece of text, indicating the location to jump to. Can be a text string enclosed in quotation marks or, more commonly, an [[XL3Address|XL3Address formula]].
 +
Can also be a web url if it starts with {{Code|http://}} or {{Code|https://}}.
 +
|-
 +
| {{Code|FriendlyName}}
 +
| The jump text or numeric value that is displayed in the cell. If FriendlyName is omitted, the cell displays the LinkLocation as the jump text
 +
|-
 +
| {{Code|LinkType}}
 +
| The type of link. If Hyperlink is used, the cell is formatted with the Excel style ''Hyperlink''
 +
{|
 +
| 1 || Hyperlink (default)
 +
|-
 +
| 2 || Double-click
 +
|-
 +
| 3 || HyperLink with submit changes on web
 +
|-
 +
| 4 || Toolbar button on web (from [[Version 8]] onwards)
 +
|}
 +
|-
 +
| {{Code|Range1,…, RangeN}}
 +
| A reference to a cell where the following Value is placed when the jump is executed. Usually an [[XL3Address|XL3Address formula]]
 +
|-
 +
| {{Code|Value,…, ValueN}}
 +
| The value that will passed to the corresponding Range
 +
|}
 +
 
 +
===Toolbar buttons===
 +
For a type 4 link, the following are the valid values for the link location argument.
 +
([[Version 8]])
 +
 
 +
{| class="wikitable"
 +
| "PRINT"
 +
|-
 +
| "SUBMITCHANGES"
 +
|-
 +
| "REFRESH"
 +
|-
 +
| "SAVETOEXCELFIXED"
 +
|-
 +
| "SAVETOEXCELLIVE"
 +
|-
 +
| "SAVETOREPOSITORY"
 +
|}
 +
 
 +
 
 +
[[Version 9]] adds the following options:
 +
 
 +
{| class="wikitable"
 +
| "PRINTSHEET"
 +
|-
 +
| "PRINTWB"
 +
|-
 +
| "SAVETOEXCELFIXEDSHEET"
 +
| (Optional) Argument after LinkType is the sheet to save, if not provided the active sheet is saved.
 +
|-
 +
| "CLOSETAB"
 +
|}
 +
 
 +
===Special Values===
 +
Some range names are treated specially by XLCubed.
 +
 
 +
{{Code|XL3Tooltip}} - The associated value is used as the tooltip, no range is updated. This is available from ([[Version 8.1]]).
 +
 
 +
==Examples==
 +
''The following XL3Link formula creates a hyperlink to the 'LinkTarget' sheet and shows in the hyperlink the text "Jump to Target Sheet"''
 +
 
 +
{{Code|1==XL3Link( XL3Address( LinkTarget!A1 ), "Jump to Target Sheet" )}}
 +
 
 +
''You can use XL3Link to jump from one sheet to another sheet in the workbook, while passing some parameters. The following formula jumps to the sheet 'LinkTarget' and sets value 'Car and Bike Stores' at {{Code|LinkTarget!A2}}.''
 +
 
 +
{{Code|1==XL3Link( XL3Address( LinkTarget!A1 ), "Jump to Target Sheet and pass 'Car and Bike Stores'", 1, XL3Address( LinkTarget!A2 ), "Car and Bike Stores" )}}
 +
 
 +
'' The XL3Link formula below creates a tool tip by passing the parameter XL3Tooltip and sets the tool tip text to be "This is a tool tip"''
 +
 
 +
{{Code|1==XL3Link(XL3Address(LinkTarget!A1),"Tooltip example",,"XL3Tooltip","This is a tool tip")}}
 +
 
 +
==See Also==
 +
* [[Formula Reference]]
 +
* [[XL3Address|XL3Address formula]]
 +
* [[Insert Link|Insert Link wizard]]
  
 
[[Category:Formulae]]
 
[[Category:Formulae]]
 +
[[Category:Report Management Formulae]]
 
[[Category:Report Management]]
 
[[Category:Report Management]]

Latest revision as of 10:45, 13 April 2017

The XL3Link() formula helps users easily build linked analysis into their reporting, as described in the diagram below from Stephen Few's book on Information Dashboard Design.

Linktodetail.png

The formula has two main functions. Firstly it jumps to another location in the workbook, like a hyperlink, and secondly it passes parameters held in one cell(s) into another cell(s).When you follow the hyperlink, XLCubed jumps to the sheet stored at LinkLocation and sets the parameters at the specified parameter addresses. Since selections for XLCubed Grids and formulae can be based on the content of a cell, they can update to reflect the passed parameter immediately.

Use the Insert Formula > XL3Link menu or ribbon item to insert the formula using a wizard. More details can be found here.

Syntax

XL3Link( [LinkLocation], [FriendlyName], [LinkType], [Range1], [Value1],…, [Range13], [Value13] )

Parameters

Parameter Description
LinkLocation A piece of text, indicating the location to jump to. Can be a text string enclosed in quotation marks or, more commonly, an XL3Address formula.

Can also be a web url if it starts with http:// or https://.

FriendlyName The jump text or numeric value that is displayed in the cell. If FriendlyName is omitted, the cell displays the LinkLocation as the jump text
LinkType The type of link. If Hyperlink is used, the cell is formatted with the Excel style Hyperlink
1 Hyperlink (default)
2 Double-click
3 HyperLink with submit changes on web
4 Toolbar button on web (from Version 8 onwards)
Range1,…, RangeN A reference to a cell where the following Value is placed when the jump is executed. Usually an XL3Address formula
Value,…, ValueN The value that will passed to the corresponding Range

Toolbar buttons

For a type 4 link, the following are the valid values for the link location argument. (Version 8)

"PRINT"
"SUBMITCHANGES"
"REFRESH"
"SAVETOEXCELFIXED"
"SAVETOEXCELLIVE"
"SAVETOREPOSITORY"


Version 9 adds the following options:

"PRINTSHEET"
"PRINTWB"
"SAVETOEXCELFIXEDSHEET" (Optional) Argument after LinkType is the sheet to save, if not provided the active sheet is saved.
"CLOSETAB"

Special Values

Some range names are treated specially by XLCubed.

XL3Tooltip - The associated value is used as the tooltip, no range is updated. This is available from (Version 8.1).

Examples

The following XL3Link formula creates a hyperlink to the 'LinkTarget' sheet and shows in the hyperlink the text "Jump to Target Sheet"

=XL3Link( XL3Address( LinkTarget!A1 ), "Jump to Target Sheet" )

You can use XL3Link to jump from one sheet to another sheet in the workbook, while passing some parameters. The following formula jumps to the sheet 'LinkTarget' and sets value 'Car and Bike Stores' at LinkTarget!A2.

=XL3Link( XL3Address( LinkTarget!A1 ), "Jump to Target Sheet and pass 'Car and Bike Stores'", 1, XL3Address( LinkTarget!A2 ), "Car and Bike Stores" )

The XL3Link formula below creates a tool tip by passing the parameter XL3Tooltip and sets the tool tip text to be "This is a tool tip"

=XL3Link(XL3Address(LinkTarget!A1),"Tooltip example",,"XL3Tooltip","This is a tool tip")

See Also