{"id":19,"date":"2012-01-21T23:13:48","date_gmt":"2012-01-22T05:13:48","guid":{"rendered":"http:\/\/xlfxs.com\/?p=19"},"modified":"2012-01-21T23:13:48","modified_gmt":"2012-01-22T05:13:48","slug":"hyperlink","status":"publish","type":"post","link":"https:\/\/xlfxs.com\/?p=19","title":{"rendered":"Hyperlink usage and HyperlinkOf UDF"},"content":{"rendered":"

The Hyperlink<\/strong><\/em> function is really powerful and yet not widely used.<\/p>\n

You can create a whole navigation system with Hyperlink<\/strong><\/em> fx or create a custom jump to link to take user to certain area.<\/p>\n

Of course the “Jump-to” link need other functions, like Match<\/em><\/strong>, Indirect<\/strong><\/em> and Index<\/strong><\/em><\/p>\n

Here is a sample of what we can do with Hyperlink<\/strong><\/em> and other functions<\/p>\n

It also has the UDF (User-defined-function) that called HyperlinkOf<\/strong><\/em> to get the actual Hyperlink address of a cell hold a regular hyperlink (created with Insert –> Hyperlink)<\/p>\n

This one has a lot of formulas to make it work well:<\/p>\n

First: we have the formula that brings the path where this Excel file is saved:<\/p>\n

=LEFT(CELL(\"filename\",$A$1),SEARCH(\"[\",CELL(\"filename\",$A$1))-1)<\/pre>\n
Then: we have the one that grabs This Excel file name:<\/p>\n
=MID(CELL(\"filename\",$A$1),SEARCH(\"[\",CELL(\"filename\",$A$1))+1,SEARCH(\"]\",CELL(\"filename\",$A$1),SEARCH(\"[\",CELL(\"filename\",$A$1)))-SEARCH(\"[\",CELL(\"filename\",$A$1))-1)<\/pre>\n
Then: The one that brings Active sheet name:<\/p>\n
=MID(CELL(\"filename\",$A$1),SEARCH(\"]\",CELL(\"filename\",$A$1))+1,500)<\/pre>\n
And off course: the one that Constructs the Hyperlink itself<\/p>\n
=HYPERLINK(IF(C10=$C$8,\"[\"&C10&\"]'\"&D10&\"'!\"&E10,IF(B10=$B$8,C10,B10&C10)))<\/pre>\n
And as a bonus, the UDF that reads the URL from a flat Hyperlink:<\/p>\n
=hyperlinkof(G3)<\/pre>\n
\"\"<\/a><\/p>\n\n","protected":false},"excerpt":{"rendered":"
The Hyperlink function is really powerful and yet not widely used. You can create a whole navigation system with Hyperlink fx or create a custom jump to link to take user to certain area. Of course the “Jump-to” link need other functions, like Match, Indirect and Index Here is a sample of what we can do…<\/p>\n
Read More “Hyperlink usage and HyperlinkOf UDF”<\/span> »<\/a><\/p>\n","protected":false},"author":1,"featured_media":71,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[70,3,79,30,31,36,40,6,10],"tags":[],"class_list":["post-19","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-cell","category-formulas","category-hyperlink","category-hyperlinkof","category-information","category-lookup_and_references","category-mid","category-standard_functions","category-udf-user-defined-functions"],"_links":{"self":[{"href":"https:\/\/xlfxs.com\/index.php?rest_route=\/wp\/v2\/posts\/19","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/xlfxs.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/xlfxs.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/xlfxs.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/xlfxs.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=19"}],"version-history":[{"count":0,"href":"https:\/\/xlfxs.com\/index.php?rest_route=\/wp\/v2\/posts\/19\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/xlfxs.com\/index.php?rest_route=\/wp\/v2\/media\/71"}],"wp:attachment":[{"href":"https:\/\/xlfxs.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=19"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/xlfxs.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=19"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/xlfxs.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=19"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}