Building Dynamically Driven Excel Hyperlinks

I recently came upon a challenge that at face value looked straight forward but actually proved to be quite tricky.

What I wanted to do was create a drop-down menu in Excel. Depending on the choice selected, a hyperlink would be created that would allow you to “jump” to a specific location in the Excel workbook based upon your selection.

The solution I hit upon is to use the Hyperlink formula with a unique twist.

First, I created two different worksheets – for this example I’ve creatively named them “Test Sheet 1” & “Test Sheet 2”.

Next, I created a drop-down list menu using the two worksheet names (cell C2 in picture below; see my previous post for details: Excel: Creating a Drop Down Menu).

Excel: Drop Down Menu Example

Finally, in cell C4 I put the following formula =HYPERLINK("#'"&C2&"'!A1","Go to "&C2) where the first part "#'"&C2"'!A1" builds the actual link #’Test Sheet 1′!A1 or #’Test Sheet 2′!A1 (depending on the drop down selection) that takes you to the A1 cell on either the Test Sheet 1 or Test Sheet 2 worksheet, and the second half "Go to "&C2 builds the text that’s displayed to the user in cell C4.

Excel: Dynamic Hyperlink Formula

Viola! Depending on the drop down item selected, the hyperlink will dynamically update and direct the user to the correct worksheet.

The tricky part was in figuring out where to put all the single & double quote marks as well as learning about using the hash mark to indicate that the URL was inside the current workbook without having to specify the full name of the workbook.

This should come in handy when building table of contents, forms, &/or surveys in Excel.

~Yosef

Windows 10 Calculator Shortcut

I’m used to pulling up the built-in Microsoft Windows calculator app when I have a quick calculation to do.
I recently discovered that with Windows 10 I don’t even need to open the calculator! I can simply open the start menu (Windows key) and start typing my math problem (including trig functions!) and it will display the answer right there in my start menu!

For all I know this has been available in Windows 8 for a while but I definitely use this tip now that I know about it!

Here’s a screenshot of what I mean – try it out yourself!

Windows 10 Start Calculator

HTTPS Protocols and Ciphers

To give a bit of background on this post –

I was researching different Firefox about:config settings for another article (to be forthcoming) and had changed a bunch of security settings to try and force Firefox to use higher grade encryption when accessing encrypted (HTTPS) websites.

Later in the day, I tried to access a website I visit very frequently and found that it wouldn’t load. I eventually realized that it must be because I had changed my Firefox security settings. The question I was stuck with was, “How do I figure out what encryption protocol and cipher a website is using in order to allow that encryption in Firefox?”. After some more research I was left with the following:

What is HTTPS & how does it work?

When you access a website, you are typically communicating over one of two protocols: HyperText Transfer Protocol (HTTP) or HyperText Transfer Protocol Secure (HTTPS). When you connect using HTTPS, all the information sent back and forth between you and the website is encrypted.

There are of course exceptions to this – for example, on your bank’s website, it may have a secure component for logging into the website but it may also have a third party advertisement that is not delivered encrypted to your browser. Depending on your browser, it may alert you when there is “mixed” content being loaded.

At a high level, when you access an HTTPS website, the server has a list of approved protocols and ciphers that it will allow communications to be encrypted with. This list is either the default list that the server comes enabled with (i.e. no one really thought about it, they just turned it on), or it’s a customized list that (hopefully) only allows the latest and greatest encryption methods.

Just like the server, your browser has a list of approved protocols and ciphers that it can use. When your browser contacts the server to load the HTTPS page, the server gives it a list of the approved encryption methods – this list is prioritized by the server’s “preference”.

Assuming your browser allows connections using one of the approved server’s methods, the browser and server conduct a “handshake” where the encryption method and a key are agreed upon and exchanged, allowing both the server & the browser to encrypt and decrypt communications between them. When the “handshake” is conducted, it is the most dangerous part of the communications process because it is done without encryption and (if done improperly) can allow for “man in the middle” (MITM) attacks or other attacks by an outside party. (I hope to cover different types of attacks and how they work in a future article.)

So what’s the difference between a protocol and a cipher? Think of encryption protocols as the method by which information is encapsulated and sent whereas ciphers are the way which information inside the capsule is scrambled (encrypted) to prevent anyone from reading the information.

Determining a Server’s allowed Protocols and Ciphers

This leads us back to my original question – if your browser does not accept the encryption types allowed by the server, how do you figure out which ones you need?

I found that there is a very handy website called http://www.ssllabs.com that has a SSL Server Test tool (available here: SSL Server Test) that allows you to plug in any website and it will determine if there are any available HTTPS connections to that website as well as give you a high level score and a very detailed breakdown of the site’s security protocols and ciphers.

There is also a Firefox plug-in available (called CipherFox) to give you this information every time you visit a website.

Tomorrow I will cover which Protocols and Ciphers are best to use & how to implement them in your Firefox browser.

Got questions or feedback? let me know in the comments below. Thanks!

Excel: Select only numbers trick

Here’s a cool trick I came across recently.

Let’s say you have a worksheet setup that has a financial calculation model. It has input cells for your initial values and then a whole bunch of calculations. At some point, you decide to clear all your initial values in your input cells in order to input new ones.

You could scroll around looking for non-formula cells or you can take advantage of an Excel feature which lets you select cells in a “special” way. To do so:

1. Select Edit, Go To (or press Ctrl-G or F5) to bring up the Go To dialog box
2. Click the “Special” button
3. Choose the Constants option, then clear all of the check boxes except Numbers (if some of the input cells in the spreadsheet accept text, leave the check mark next to Text)
4. Click OK, and Excel will select all of the non-formula cells which contain a value
5. Press the Delete key, and all numbers will be deleted – but your formulas will remain intact

Excel: The dangers of “Set precision as displayed”

The other day, a co-worker of mine called me and asked for help with a spreadsheet. He had a number of calculations and when he got to the end of them the number looked wrong so he calculated the number manually on a calculator and got a different answer!

Turns out, he did not realize that by default, Excel rounds calculations and only displays the number of decimals that you specify (defaults to 2 decimals when formated as a number). It took me a while to understand what he was talking about too because I came at it from the other direction, I never though that Excel would only calculate to the precision of the displayed value!

There is actually a buried setting that will make Excel calculate cells with as much precision as the displayed format. To enable/disable this option, go to File > Options > Advanced > When calculating this workbook: > “Set precision as displayed”.

However, there are dangers to using this setting and I want to make sure they are clear before you go enabling it.

1. I think it’s safe to assume that most people do not use (or even know) about this option. As such, most people work with Excel expecting it to round cell values visually but not round the actual background calculations. If you turn on this feature & then send the sheet to someone else, they could completely mess up your values if they start playing with formatting or inputting new numbers.
2. If you turn on this feature after you have already built a spreadsheet, your spreadsheet will permanently lose all accuracy.

For an example of how this works, here’s a simple table I built to calculate fuel costs:

Original Values – Formatting set to 3 decimals for Cost / Gallon column

	Gallons	Cost / Gallon	Subtotal
	7	$2.999	$20.99
	5	$3.759	$18.80
	8	$3.699	$29.59
Total			$69.38

“Set precision as displayed” Enabled – Formatting set to 2 decimals for Cost / Gallon column

	Gallons	Cost / Gallon	Subtotal
	7	$3.00	$21.00
	5	$3.76	$18.80
	8	$3.70	$29.60
Total			$69.40

As you can see, the total amount has rounded up by 2 cents. If you go and add decimal places back to the Cost / Gallon, this rounding will not change.

I am curious to know how many other people have run into this issue – Have you used this setting?