The XL3Link() formula helps users quickly add linked analysis to their reports. 

The formula has two main functions. First, it jumps to another location in the workbook, similar to a link. Second, it passes parameters held in one cell into another cell. When you choose the link, Anaplan XL goes to the sheet at LinkLocation and sets the parameters at the addresses you gave it. Anaplan XL grids and formulas can be chosen based on the content of a cell. This means that they can be updated to show the passed parameter right away.

Use the Insert Formula > XL3Link menu or ribbon item to insert the formula using a wizard. 

Syntax

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

Parameters

ParameterDescription
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://.

FriendlyNameThe 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 Link 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.

8 - HyperLink with submit changes and recalculation on Web. Used when the link output or action references formulas that must be recalculated based on the submitted changes.

Range1,…, RangeNA reference to a cell where the value is placed when the jump is executed. Usually an XL3Address formula.
Value,…, ValueNThe value that's passed to the corresponding range.

Valid values toolbar button on web

For a type 4 link, these are the valid values for the link location argument. 

  • "CLOSETAB"
  • "PRINT"
  • "PRINTSHEET"
  • "PRINTWB"
  • "REFRESH"
  • "SAVETOEXCELFIXED"
  • "SAVETOEXCELFIXEDSHEET" (Optional) Argument after LinkType is the sheet to save, if not provided the active sheet is saved.
  • "SAVETOEXCELLIVE"
  • "SAVETOREPOSITORY"
  • "SUBMITCHANGES"

Special values

Some range names are treated specially by Anaplan XL.

  • XL3Tooltip - The associated value is used as the tooltip, no range is updated. 
  • XL3Target - The target frame for http(s) links. 
  • XL3UndoEditedFormulae - Applies only to web when the XL3Link submits changes. When set to TRUE any cells with formulas that are changed will have their formulas restored after the link has been activated.
  • XL3Confirm - Asks user to confirm before executing the link, the associated parameter is the message to display.

Examples 

The XL3Link formula creates a link to the LinkTarget sheet and shows in the link 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. This 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" )

This XL3Link formula creates a tooltip by passing the parameter XL3Tooltip, and sets the tooltip text to be This is a tooltip:

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

Usage with IF() 

Sometimes, you may want to have the parameters to the XL3Link() function depend on other criteria in the workbook. You can do this, but it's important to have only one XL3Link() in your formula, so

Use this format:

=XL3Link("Sheet1!A1",IF(A2, "Text 1", "Text 2"),,"Sheet1!A3",IF(A2, "value 1", "value 2"))

Not this format:

=IF(A2, XL3Link("Sheet1!A1","Text 1",,"Sheet1!A3","value 1"), XL3Link("Sheet1!A1","Text 2",,"Sheet1!A3","value 2"))


This is because the link is registered when the XL3Link formula is evaluated. In the second case, both links will be registered for the same cell, so you won't get the required result.