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

For more on custom number formats  take a look at Jon von der Heyden’s comprehensive guide to custom number formats.

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

Share This

Please share this or leave a comment and I'll make sure you get a personal reply.

Leave a Comment

Current day month ye@r *

{ 2 comments… read them below or add one }

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

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

Previous post:

Next post: