HYPERLINK Function

image_pdfimage_print

The Jedox function HYPERLINK() has more functionality than the corresponding Excel counterpart.

Description

HYPERLINK(link_location, friendly_name, screen_tip, source1, destination1[, source2, destination2, …])

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!named_range

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!named_range”

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 URL, source1 is key, and destination1 is value in URL parameters list:
http://www.google.com?source1=destination1&source2=destination2

Target Types
As of version 3.2, 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

Examples

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).

Starting with Jedox version 5.1, you can also specify a relative location in the formula HYPERLINK() using an expression such as:
=HYPERLINK(".../Planning/Data Entry.wss",…).

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

image_pdfimage_print
Was this post helpful?
NoYes (+1 rating, 5 votes)
Loading...