• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Factor 21 Hyperlink Triptych

You are here: Home / Excel Formulas / Excel Factor 21 Hyperlink Triptych
Excel Factor 21 Hyperlink Triptych
October 31, 2012 by Mynda Treacy
These Excel Factor tips were sent in by Zoran Stanojevic of Belgrade, Serbia.

Words by Mynda Treacy

Zoran has prepared 3 distinct ways we can apply hyperlinks that dynamically update depending on the selection we make.

The selection can be controlled with either a data validation list or a spin button form control.

Download the workbook.

Warning! These are advanced tips. I am not going to do step by step instructions to the extent that I usually do.

Instead I’m going to focus on the unique aspects of Zoran’s work and allow you to download the workbook to see ‘under the bonnet’ to fully understand the intricacies.

1. Hyperlink with Data Validation

Hyperlinked Data Validation List

This first example is a hyperlinked data validation list. Zoran learnt this tip from the Frankens Team, and you can learn how to wrangle Excel to allow you to insert a hyperlink to a dynamic named range used in a data validation list here on their blog.

Bonus tip:

One of the downsides of data validation lists is the down arrow indicator only appears if the cell is selected.

To overcome this Zoran has added a custom number format to the cell containing the data validation list so that it shows an upside down triangle symbol appended to the sheet name, plus it displays the font in blue, the default for a hyperlink.

Excel Custom Number Format

The Downside of Hyperlinks with Data Validation

You need to use either your arrow keys to navigate to the cell, or Go To (CTRL+G), or click and hold the mouse until the fat cross appears. Otherwise you execute the hyperlink.

The next 3 examples are variations of one another.

2.1 Spin Button with Hyperlink 1

Hyperlinked Spin Button List

Features:

A spin button form control manages the selection.  The form control output is in cell I2 and has a minimum value of 1 and maximum value of 8.

Below are the cells that make up the named range ‘Sheets_List’. You can see from the count indicator that there are 10 items in the list.

Excel Named Range

An INDEX function returns the sheet name from the ‘Sheets_List’ with these formulas:

Cell D2

=INDEX(Sheets_List,9-$I$2)

Cell D3

=INDEX(Sheets_List,10-$I$2)

Cell D4

=INDEX(Sheets_List,11-$I$2)

Excel spin button list

The hyperlinks are managed using 3 separate named formulas; one for each item visible in the list. Scrolling through the list changes the form control output in cell I2 and dynamically updates both the name displayed in the list and the hyperlink.

Excel Named Formulas

To insert the named formulas you need to use the Frankens Team hyperlink trick mentioned in the first example above.

2.2 Spin Button with Hyperlink 2

Spin button with hyperlink

Unlike the previous example, this uses the HYPERLINK function to create the dynamic links:

Excel Named Formulas

You can read more on the HYPERLINK function and the use of the # symbol here.

2.3 Spin Button, Hyperlink and Cell Formatting

Hyperlinke Funcction

This uses some simple cell formatting to draw attention to the middle choice.

The spin button control settings have a minimum value of 0 and maximum value of 9, as opposed to 1 and 8 respectively for the previous 2 examples. As a result, the formulas for each choice are slightly different to handle errors:

Choice 1:

=IF($I$2<9,HYPERLINK("#"&ADDRESS(2,2,,,INDEX(Sheets_List,9-$I$2)),INDEX(Sheets_List,9-$I$2)),"")

Choice 2:

=HYPERLINK("#"&ADDRESS(2,2,,,INDEX(Sheets_List,10-$I$2)),INDEX(Sheets_List,10-$I$2))

Choice 3:

=IF($I$2,HYPERLINK("#"&ADDRESS(2,2,,,INDEX(Sheets_List,11-$I$2)),INDEX(Sheets_List,11-$I$2)),"")

3 Spin Button List Hyperlink and a Zillion Other Tricks!

This is the Full Monty!

Excel Hyperlinks with Spin button list

Like the previous examples, this also uses a Spin Button, but because quarterly groups of months are displayed, the formula behind each month is unique:

Excel Named Formulas

Cell K2:

=HYPERLINK("#"&ADDRESS(2,2,,,TEXT(90*K5-60,"[$-409]mmm")),UPPER(TEXT(90*K5-60,LangID&"mmmm")))

Cell K3:

=HYPERLINK("#"&ADDRESS(2,2,,,TEXT(90*K5-30,"[$-409]mmm")),UPPER(TEXT(90*K5-30,LangID&"mmmm")))

Cell K4:

=HYPERLINK("#"&ADDRESS(2,2,,,TEXT(90*K5,"[$-409]mmm")),UPPER(TEXT(90*K5,LangID&"mmmm")))

How to calculate a date using the TEXT function

First let’s understand how the month is derived for the link_location argument of the HYPERLINK Function.

Remember the spin button output is in cell K5 (currently containing 1) and the first TEXT function is evaluating the month name.

Using cell K2 as our example:

=HYPERLINK("#"&ADDRESS(2,2,,,TEXT(90*K5-60,"[$-409]mmm")),UPPER(TEXT(90*K5-60,LangID&"mmmm")))

Step 1: TEXT(90*1-60,"[$-409]mmm")

Step 2: TEXT(30,"[$-409]mmm")

Step 3: TEXT(30/01/1900,"[$-409]mmm")

Step 4: TEXT(Jan)

Hint: if you type the number 30 into a cell and format the cell as a ‘short date’ it will display 30/01/1900 or 01/30/1900 if you use mm/dd/yyyy format.

That's because all dates and time in Excel are given a serial number. The serial number for 30th January 1900 is 30.

The [$-409] forces it to always evaluate in English. This is because the worksheet tab names are in English and this part of the formula is returning the address for the hyperlink.

The ‘mmm’ formats it to the first 3 letters of the month name (again, because all of the worksheet names are only the first 3 letters of each month).

So, what about the other months…

February:

Step 1: TEXT(90*1-30,"[$-409]mmm")

Step 2: TEXT(60,"[$-409]mmm")

Step 3: TEXT(29/02/1900,"[$-409]mmm")

Step 4: TEXT(Feb)

March:

Step 1: TEXT(90*1,"[$-409]mmm")

Step 2: TEXT(90,"[$-409]mmm")

Step 3: TEXT(30/03/1900,"[$-409]mmm")

Step 4: TEXT(Mar)

As the spin button output value in cell K5 increases the month dynamically updates.

How to Translate Language

Now we’ll look at the second TEXT function used for the friendly_text argument of the HYPERLINK Function to understand how Excel can translate languages.

Again using cell K2 as our example:

=HYPERLINK("#"&ADDRESS(2,2,,,TEXT(90*K5-60,"[$-409]mmm")),UPPER(TEXT(90*K5-60,LangID&"mmmm")))

We already know how the first part evaluates the date from the previous example so we’ll focus on the translation.

Step 1: TEXT(90*K5-60,LangID&"mmmm")

LangID is a named formula:

=VLOOKUP('Index page'!$K$16,LNGnym:LangIDs,2,TRUE)

Named ranges LNGnym and LangIDs used in the above formula are the two columns of the Language ID’s table below. K16 is the data validation list for the language choice.

Excel choose language

Is your language missing? Click here for a complete list of language ID numbers.

Custom Number Format

Again, Zoran has used a clever custom number format to display the spin button output with some meaningful text:

Excel custom number format

Excel custom number format

More on custom number formats in this comprehensive Excel custom number format guide.

Dynamic Hyperlinks

This example incorporates two techniques for the hyperlinks.

The months use the HYPERLINK Function, and the 'Qt.# Summary' in cell K5 uses the Frankens Team trick.

Excel custom number format

UPDATE: Zoran has made some improvements on examples 2.1 and 2.2 above which simplify them.

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the new Excel Workbook here.

Thanks for sharing your ideas with us, Zoran.

Zoran

“My name is Zoran and I am from Serbia. I have worked with Excel since my first employment back in 1996 (Office 97) and I was immediately drawn to VBA. Although my main expertise was wider IT administration, Excel Macro/VBA development gets an ever bigger share.

Serbians have many ethnicities and all have rights to use their own language (including the alphabet) in formal communication with the public services as well as through formal education. Serbia itself has two alphabets: Latin and Cyrillic and apart from that, in the Autonomous Province of Vojvodina another 5 official languages exist.

In January I moved back to my hometown Subotica where I work in the City Administration IT department. This year I was involved in the preparation processes for the Serbian elections in my municipality, where I took part in data mining and reporting during and after the elections. It was challenging since elections were held on all levels: both parliamentary and presidential, for the state, autonomous province, districts and down to the very cities and towns.

The City Administration in Subotica has one of the most efficient and best organized public services in the country and the IT Department is the skeleton of the system, so it is a great experience to work here, and be part of it.”

Vote for Zoran

If you’d like to vote for Zoran's tips (in X-factor voting style) use the buttons below to Like this on Facebook, Tweet about it on Twitter, +1 it on Google, Share it on LinkedIn, or leave a comment to thank Zoran for taking the time to suggest this tip….or all of the above 🙂

Excel Factor 21 Hyperlink Triptych

More Excel_Factor Posts

Excel Factor Voting Roundup

Excel Factor Voting Roundup

Excel Factor 20 Custom Number Format Disguise

Excel Factor 20 Custom Number Format Disguise

Excel Factor 19 Dynamic Dependent Data Validation

Excel Factor 19 Dynamic Dependent Data Validation

Excel Factor 18 Dynamic Hyperlinks and the Magic Hash

Excel Factor 18 Dynamic Hyperlinks and the Magic Hash

The Excel HYPERLINK function creates links to places inside or outside of your Excel file. I'll show you a shortcut for creating them that few people know.
Excel Factor 17 Lookup and Return Multiple Matches

Excel Factor 17 Lookup and Return Multiple Matches

Excel Factor 16 Dynamic Lookup

Excel Factor 16 Dynamic Lookup

Excel Factor 15 The Lazy Lookup

Excel Factor 15 The Lazy Lookup

Excel Factor 14 Interleave Data from Two Columns into One

Excel Factor 14 Interleave Data from Two Columns into One

Excel Factor 13 Handy Tips & Tricks

Excel Factor 13 Handy Tips & Tricks

Excel Factor 12 Secret EVALUATE Function

Excel Factor 12 Secret EVALUATE Function

More Hyperlink Posts

Create Hyperlinked List of Files in Subfolders

Create Hyperlinked List of Files in Subfolders

Using a little VBA we can create a hyperlinked list of files from subfolders right in our Excel worksheet.
Create Hyperlinked List of Files in a Folder Using VBA

Create Hyperlinked List of Files in a Folder Using VBA

Using Excel VBA, we can create a hyperlinked list of files from a folder in our worksheet
Excel Hyperlink Buttons

Excel Hyperlink Buttons

Easy navigation in your Excel workbooks using shapes and hyperlinks to switch between sheets. Create an Index page for all sheets in the workbook.
Save Time with Hyperlinks for Word, Excel and Outlook

Save Time with Hyperlinks for Word, Excel and Outlook

Use hyperlinks to make navigating in your documents easier. You can also link to external files on your hard drive to make them easier to find and open.

More Excel Formulas Posts

excel formula by example

Excel Formula by Example

Excel can now write a formula by example. Simply give it an example or two of the result and Excel will write the formula.
ai-aided excel formula editor

AI Aided Excel Formula Editor

Save time with this free AI Excel formula editor add-in that writes, edits, improves and interprets formulas for you!
top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.


Category: Excel FormulasTag: Excel_Factor, Hyperlink
Previous Post:Excel Factor 20 Custom Number Format DisguiseExcel Factor 20 Custom Number Format Disguise
Next Post:Excel Factor Voting RoundupExcel Factor Voting Roundup

Reader Interactions

Comments

  1. Dianne

    December 22, 2022 at 8:55 pm

    Can any of these be modified for a mac? IIt’s great for at work on my windows computer, but have not figured out how to make it work on mac where I work most of the time? Thanks

    Reply
    • Mynda Treacy

      December 23, 2022 at 7:59 am

      Hi Dianne,

      I don’t have a Mac, but I believe that form controls are not available on a Mac. You could try using data validation lists instead and modifying the formulas accordingly. If you get stuck, you can post your question and sample Excel file on our forum where we can help you further: https://www.myonlinetraininghub.com/excel-forum

      Mynda

      Reply
  2. Mahebubkhan

    March 19, 2017 at 3:45 pm

    Sir..
    How to hidden hiperlink?

    Reply
    • Mynda Treacy

      March 19, 2017 at 7:20 pm

      Here is a link to a hyperlink tutorial.

      Mynda

      Reply
  3. Zoran Stanojević

    November 1, 2012 at 1:42 am

    Without explicit use of the LangID, Excel would assume the language you have set in Regional settings, so everything appear as usual, until you send the workbook to someone with the different default language. Therefore, it could be a good practice to ‘lock’ the language to the one used in formulas (note that it applies only for named lists, otherwise does not matter).

    As an exercise with LangIDs try playing with another international list of names Excel ‘understands’: the names of the weekdays!
    =TEXT({1;2;3;4;5;6;7},LangID&”dddd”)

    Reply
  4. Turab

    October 31, 2012 at 10:19 pm

    Zoran
    Indeed good use of hyperlink technique. I have to still fully understand the same but the presentation is good and I am sure i will be able to use it in one of my application. Thanks for sharing the knowledge

    Reply

Leave a Reply Cancel reply

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

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

239 Excel Keyboard Shortcuts

Download Free PDF

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.