HYPERLINK Function

image_pdfimage_print

As in Excel, the Jedox HYPERLINK() function can create a shortcut to another location in the current workbook, or opens a document stored on a network server or other location. In addition, the HYPERLINK() function also allows values to be set in or transferred to spreadsheet cells, named ranges, or variables when the link is clicked. 

The syntax for the function is HYPERLINK(link_location, friendly_name, screen_tip, source1, destination1[, source2, destination2, …]) and the arguments are outlined below.

link_location:

“SELF”

Link to active workbook, Sheet1, A1. This link will also work after renaming or moving the workbook.

“[SELF]Sheet2!A5”

Link to active workbook, Sheet2, A5. This link will also work after renaming or moving the workbook.

“/Jedox/Financials/Accounting/Demo.wss”

Link to workbook Demo in group “Jedox”, hierarchy “Financials”, folder “Accounting”.

“/Jedox/Financials/Accounting/Demo.wss#Frame2”

In framesets: open workbook Demo.wss in the frame called “Frame2”.

“[/Jedox/Financials/Accounting
/Demo.wss]Sheet2!A1″

Open workbook Demo and select A1 on Sheet2. Absolute references with $ are not necessary and disallowed here.

“[/Jedox/Financials/Accounting
/Demo.wss]Sheet2!A1#Frame2″

In framesets: open workbook Demo and select A1 on Sheet2, in Frame2.

“[/Jedox/Financials/Accounting
/Demo.wss]’Some sheet’!A2:B3″

Open workbook Demo and select range A2:B3 on sheet ‘Some sheet’ (use singe quotes for sheet names with spaces. Absolute references with $ are not necessary and disallowed here).

“[/Jedox/Financials/Accounting/Demo.wss]A2”

Open workbook Demo and select A2 on the first sheet. Absolute references with $ are not necessary and disallowed here.

“[/Jedox/Financials/Accounting
/Demo.wss]SomeRange”

Open workbook Demo and select range that named range “SomeRange” with scope on the first sheet refers to.

“[/Jedox/Financials/Accounting
/Demo.wss]Sheet2!SomeRange”

Open workbook Demo and select range that named range “SomeRange” with scope on Sheet2 refers to.

“/Jedox/Financials/Documentation/Manual.pdf”

Link to PDF document Manual.pdf in group “Jedox”, hierarchy “Financials”, folder “Documentation”.

http://www.google.com/

Link to URL.

“mailto:test@example.com?subject=some text!&Body=Test”

Link that opens mail client and creates email to recipient with subject and body.

A5 or $A$5

Use content from another cell on the worksheet for link_location.

friendly_name:

“Some hyperlink caption”

Regular string.

B5 or $B$5

Use content from another cell on the worksheet for friendly_name.

CONCATENATE(B2,C2)

Result from formula.


screen_tip:

“Some hyperlink tip”

Regular string. The usage of the html tags br (line break), b (bold) and i (italic) is supported. Other html tags are not supported.

C5 or $C$5

Use content from another cell on the worksheet for screen_tip.

CONCATENATE(B2,C2)

Result from formula.

Data transfers

sourceN:

B7

Use content from cell B7 on active sheet.

A2:B3

Use content from range A2:B3 on active sheet.

Sheet2!A2:B3

Use content from range A2:B3 on sheet “Sheet2”.

SomeRange

Use content from range that named range “SomeRange” with scope on active sheet refers to.

Sheet2!SomeRange

Use content from range that named range “SomeRange” with scope on Sheet2 refers to.

@var1

Use content from variable “var1”.

“Some text”

Constant value.

{“value1″,”value2″,”value3”}

List of constant values.

destinationN:

B7

Set cell B7 on active sheet.

A2:B3

Set range A2:B3 on active sheet.

“Sheet2!A2:B3”

Set range A2:B3 on sheet “Sheet2”.

SomeRange

Set range that named range “SomeRange” with scope on active sheet refers to.

Sheet2!SomeRange

Set range that named range “SomeRange” with scope on Sheet2 refers to.

“@var1”

Set value of variable “var1”.

applicable only by URL link_location

“Some text”

Constant value.

{“value1″,”value2″,”value3”}

List of constant values.

Note: when link_location is a URL, source1 is key, and destination1 is value in URL parameters list:

http://www.google.com?source1=destination1&source2=destination2

Target Types
You can specify a target type that determines where and how the hyperlink will be opened. In Hyperlink Spreadsheet functions, the target type is appended with a leading hashmark (#) to the link target.

The types are:

  • _new: target link will be opened in new WSS tab (DEFAULT)
  • _blank: target link will be opened in new browser window
  • _self: current file will be closed and target link will be opened in the same tab
  • _top: closes the current frameset (or current file) and opens link in the same tab
  • FrameN: should only be used in a frameset context. Opens the linked document in FrameN of the same WSS window

Relative Hyperlinks

You can also specify a relative location in the formula HYPERLINK(). This allows to not fully specify the folder path to a specific report, but refer to the target report relative to the location of the current report. The syntax is similar to UNIX-like path expression. For example, to link from the current report to another one called “Data Entry” which is located in the same directory, the syntax is:

=HYPERLINK("./Data Entry.wss",…)

To open a report which is located i another folder called “Planning”, which is located on the same level as the folder of the current report, the syntax is:

=HYPERLINK("../Planning/Data Entry.wss",…)

The following examples serve to demonstrate the capabilities of this function:

The file “Function_HYPERLINK” is stored in “Default/Public Files”.

Enter the following function in A4 with “Insert – Function”:

=HYPERLINK("/Demo Spreadsheets/Demos/A.wss#Frame3","Hyperlinktext","Mousetext",A1:D2,"Sheet4!A10:D11")

The target area must be present, so create the file “Demo Spreadsheets/Demos/A.wss” with “Sheet4” before the execution of the function. The function above copies the range A1:D2 of the file “Function_HYPERLINK”, then opens the file “Demo Spreadsheets/Demos/A.wss” and pastes the copied area in the cells A10:D11 of “Sheet4”. Note: the source area must be entered without quotes, the target area with double quotes.

The function can therefore also transfer cell values, and for this you can also enter a range of up to 13 source/target mappings.

By adding “#frame3”, the file “A.wss” will be opened in “frame3” if the function is called in a frameset and “frame3” exists (see Frameset). If these conditions are not met, then “A.wss” will open in a new tab.

“Mousetext” displays the path to the target file when you hold the cursor over the link. To prevent displaying the path, use “Mousetext#simple” instead.

If the display text is a number (e.g. a PALO.DATA formula) you need to use the TEXT() function to format this number. The normal number formatting via the “Format cells” dialog will not work in this case.

Example:

=HYPERLINK("[SELF]Sheet2!A20",TEXT(PALO.DATA("localhost/Demo","Sales","All Products","Europe","Year","All Years","All Datatypes","Units"),"#,##0.00"),"Tip")

Example with position and size information for the target browser window. This information is only possible with the options #_blank and #_new:

=HYPERLINK("/Default/Public Files/target.wss#_blank,top=200,left=200,width=300,height=300","My link","My tooltip")

Example with position and size information and additional information for the target browser window (address bar, menu bar, window resizable, scrollbars, status bar, toolbar, each with =[yes|no]):

=HYPERLINK("/Default/Public Files/target.wss#_blank,top=200,left=200,width=300,height=300, location=yes,menubar=no,resizable=yes,scrollbars=yes,status=no,toolbar=no","My link","My tooltip")

This additional information is only possible with the option #_blank and is only for the browser window (e.g. a Jedox Spreadsheet has always scroll bars).

Note: when a DynaRange should react to a transferred value, a variable must be used as destination of the hyperlink.

image_pdfimage_print