Skip to content

Excel Functions

Excel f(x)s = Excel Functions

  • Blank (isempty) VS “” (null string) Excel User tips
  • 6174 Kaprekar’s constant Basic Math
  • Hello world! Uncategorized
  • WorksheetName and WorksheetsNo UDFs Lookup and References
  • Spreadsheet Compare application Excel User tips
  • Scorring, saving multiple outputs in 1 number Basic Math
  • Lesson learned: Do not trust Google Sheets function Google Sheets
  • CTRL+Y a mini macro Excel User tips

Hyperlink usage and HyperlinkOf UDF

Posted on January 21, 2012 By ANmar No Comments on Hyperlink usage and HyperlinkOf UDF

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 with Hyperlink and other functions

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

This one has a lot of formulas to make it work well:

First: we have the formula that brings the path where this Excel file is saved:

=LEFT(CELL("filename",$A$1),SEARCH("[",CELL("filename",$A$1))-1)

Then: we have the one that grabs This Excel file name:

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

Then: The one that brings Active sheet name:

=MID(CELL("filename",$A$1),SEARCH("]",CELL("filename",$A$1))+1,500)

And off course: the one that Constructs the Hyperlink itself

=HYPERLINK(IF(C10=$C$8,"["&C10&"]'"&D10&"'!"&E10,IF(B10=$B$8,C10,B10&C10)))

And as a bonus, the UDF that reads the URL from a flat Hyperlink:

=hyperlinkof(G3)

CELL, Formulas - combined functions, HYPERLINK, HyperlinkOf, Information, Lookup and References, MID, Standard functions, User-Defined f(x)s = UDF

Post navigation

Previous Post: Excel Functions = XL f(x)s
Next Post: MID + SEARCH to convert cell to rows

Related Posts

  • OFFSET+MATCH Formulas - combined functions
  • Count how many times letter found Formulas - combined functions
  • Looplist – Repeating months using formulas Basic Math
  • The Excel “&” bug Excel User tips
  • Skills Grid Conditional Formatting
  • Unix DateTime Number Basic Math

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Recent Posts

  • Calculate above and below me
  • Stale values (Scratched functions)
  • Middle name + 1st name
  • LastSunday, LastSaturday
  • Paste Special Percentage

Archives

Categories

  • Array formula (1)
  • Formulas – combined functions (58)
  • Google Sheets (2)
  • Non-functions (36)
    • ActiveX controls (2)
    • Conditional Formatting (7)
    • Data Validation (7)
    • Excel User tips (19)
    • Format Cells (6)
    • Graphics (4)
    • Names (3)
  • Standard functions (83)
    • Basic Math (18)
    • Date and Time (16)
      • DATE (9)
      • Hour (1)
      • MONTH (7)
      • NETWORKDAYS (2)
      • TODAY (7)
      • WEEKDAY (8)
      • WEEKNUM (1)
      • YEAR (8)
    • Engineering (1)
      • CONVERT (1)
    • Information (6)
      • CELL (4)
      • ISERROR (1)
      • N (1)
    • Logical (31)
      • AND (5)
      • IF (25)
      • IFERROR (7)
      • IFNA (1)
      • IFS (1)
      • ISERROR (1)
      • OR (2)
    • Lookup and References (36)
      • ADDRESS (4)
      • CHOOSE (3)
      • COLUMN (7)
      • FORMULATEXT (1)
      • HLOOKUP (1)
      • HYPERLINK (2)
      • INDEX (5)
      • INDIRECT (6)
      • MATCH (12)
      • OFFSET (15)
      • ROW (11)
      • VLOOKUP (5)
    • Math and Trig (24)
      • ABS (1)
      • CEILING (1)
      • COUNT (1)
      • COUNTA (4)
      • INT (9)
      • LARGE (2)
      • MAX (1)
      • Min (2)
      • SMALL (3)
      • SUM (3)
      • SUMIF (1)
      • SUMIFS (2)
      • SUMPRODUCT (1)
    • Statistical (6)
      • Average (1)
      • COUNTIF (4)
      • COUNTIFS (1)
    • Texts and Strings (24)
      • CHAR (5)
      • CONCATINATE (1)
      • FIND (1)
      • LEFT (8)
      • Len (4)
      • MID (9)
      • REPT (3)
      • RIGHT (2)
      • SEARCH (9)
      • STRING (1)
      • SUBSTITUTE (2)
      • TEXT (4)
      • TRIM (2)
      • VALUE (2)
  • Uncategorized (2)
  • User-Defined f(x)s = UDF (3)
    • HyperlinkOf (1)
  • Worksheet (16)
  • XLfxs (2)

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org
  • FORMULATEXT to show formulas FORMULATEXT
  • Multiple Dual-Validations COUNTA
  • Jan8 date format AND
  • Tally chart in functions Formulas - combined functions
  • Looplist – Repeating months using formulas Basic Math
  • CHOOSE – tree of decisions CHOOSE
  • 0% interest promotion Basic Math
  • Extract Address into table Formulas - combined functions

Copyright © 2026 Excel Functions.

Powered by PressBook News Dark theme