Excel Tip - Clickable References within a Spreadsheet

Perhaps this is an Excel 'trick' that others know of already but I will share none-the-less! 

I spent a little time figuring this one out and wanted to share the solution to this nifty feature of Excel. I have a spreadsheet with multiple worksheets and on the Test Cases worksheet, I wanted the cell contents to be a clickable link/reference to a related cell on the Design worksheet to tie in a specific test case - to the original programming change required.

This is where the HYPERLINK function comes in very handy - it can be used to hyperlink to not only an external file or URL but also to a clickable cell reference, which is exactly what I wanted. The two parameters are the reference to the cell and an optional "Friendly Name" . The trick is to specify the full worksheet & cell address in the first parameter, enclosing with double quotes and preceding with the # symbol. The second parameter, the friendly name - is what will be displayed in source cell, if you don't want technical gobbley-gook displayed. You'll see from the two screenshots below, that I have a formula in cell B3 of the Test Cases worksheet and by specifying the "#Design!B3" as the first parameter, I've created a nifty link so that when the user clicks on it, it brings them to the related cell in the "Design" worksheet.

Test Cases Worksheet with Link to Related Cell in the Design Worksheet

Design Worksheet Referenced by Clickable Link in the Test Cases Worksheet

The Order of Things

One of the great things about computers and software is that it can very quickly and accurately alphabetize lists.  This sort of task is perfect for a processor as it can order a list of 100 names if literally the blink of an eye.  Contrast the same task to a human which will take in the order of numerous minutes to accomplish.  However, one thing that computers don’t deal well with is exceptions to rules.  I was recently setting up a computer and I had setup numerous login IDs and the problem that I faced was that the name of the primary user of the computer was in the middle of the alphabet and I wanted this user listed first as she would be the main user of the computer. 

In order to override how a computer sorts, it’s important to understand how sorting is done in the first place.  Each character on the keyboards is assigned a numeric value – and the sorting is then done by comparing two characters to see which has the lower value.  The numbers 0-9 have a lower sequence than the letters A-Z followed by a-z.  The trick is in understanding that the <space> bar generates a character value just as any displayed character does and as such the name “John Smith” actually contains 10 characters (4 for the first name a space and then 5 for the last name).  Using this knowledge, a SPACE character can be added to the beginning of a name to force it towards the top of the list. 

To illustrate, let’s consider the following list of people without any special prefix and the resulting sort order:

Bob

Jane

John

Peter

Sam

If John was to be the primary user of the computer – and the name that is desired to be at the top of the list, then adding a space to the front of his name will result in the reordered list

 John

Bob

Jane

Peter

Sam

One caveat to the above approach, sometimes software or websites will remove all leading or trailing spaces, so this technique won’t work.  The alternative in this case is to use some other character other than the space.

- John

Bob

Jane

Peter

Sam

What a Tangled Web We Weave

With apologies to Shakespeare, this month’s technology article has nothing to do with deception, but I think that the first part of this famous line is very appropriate as it relates to today’s technology. As technology becomes more sophisticated and complicated, it begins to weave itself in and out of the fabric of every aspect of our society. So much so, that it can be exceedingly difficult to unweave the technology from our daily lives. In particular, I’ve been following a couple of aspects of technology over the past year and what I am seeing concerns me as they both offer wonderful futures but at the same time, I am convinced that the impact of these technologies hasn’t been fully investigated and thought through and by the time that this has been done, it will be next to impossible to unweave these technologies from the fabric of our society.

I have been following the development of 3D printing literally since it was just a concept. I think that in general 3D printing will have phenomenal benefits in a wide variety of applications, but as we’re seeing now, once the lid of possibilities has been lifted and therein lies the concern as not all applications are good applications.

Already, manufacturers of 3D printer “plans” have distributed the schematics for a working pistol made completely from 3D generated parts. The ability for anyone who has access to a 3D printer being able to produce any type of weapon – effectively bypassing – criminal code laws is staggering.

Given that Pirate Bay has recently announced that they will support and administer a 3D Design file-sharing repository, I have significant concerns that there will be not only be issues with there being various designs of weapons, but also the doors will be opened wide for counterfeit parts and the mass production of items and parts that are currently protected by copyright. This technology will either be a lawyer’s dream or nightmare.

The other area where I have concerns is with the rapidly developing prototype of autonomous – or self-driving – cars. Again, this sounds wonderful in concept and in some aspects, our roads will be safer as – in theory – a vehicle can make decisions quicker and more accurately but there’s a plethora of issues that I’m not convinced have been thought through properly.

First and foremost – to what extent will the driver still be responsible for the operation of the vehicle and any ensuing accidents? Although the current generation of autonomous automobiles is predicated on the premise that the driver has to be actively engaged and ready to take control, I wonder how much an individual’s attention will be on the road if their hands aren’t on the steering wheel and constantly – and actively – observing conditions and reacting accordingly. One of the theories of a society where there are autonomous vehicles is that they will be able to communicate with each other which will make roads safe. If 4 autonomous vehicles approach an intersection with a 4 way stop then the cars could communicate with each other and the order of precedence could be evaluated and shared, but what if one of the vehicles is driven by a human, how will this factor in the collective’s decisions as to how to proceed.

My primary concern though is that with the advent of technology, there will always be those that will exploit any level of security to compromise the device. We’re seeing it with any technology currently on the marketplace, so how can we put our lives in the balance with a vehicle that’s infected with malware?



There’s no question that our society benefits from technology, but as in these two cases, technology is being developed without the proper foresight and protocols in place to ensure that the risks are minimized. It’s next to impossible to unweave this technology from the fabric of the society, it’s in everyone’s best interest that the pros and cons have been weighed and evaluated and contingencies developed.

Addicted to...Technology

As I was pondering a topic for this month’s technology article, I read a story in the local newspaper that piqued my interest. The news story is about a family in Guelph who has shunned technology for a time and are trying to live as if it were 1986. The title for this month’s article comes from one of the hit songs in 1986 “Addicted To Love” which I have taken creative liberties as it seems completely appropriate to use “Addicted to Technology” as the headline.

I started to read what I considered to be a light-hearted article and as I did so, I realized that this thought of being addicted to technology is a very fundamental theme for my blog. I not only appreciate technology but in a lot of ways, I’d be completely lost without it. The technologies that the family refused to use such as cellphones, email, Google or a GPS, well, it makes me shudder at the thought of being without it.

The question isn’t if we could live without technology – humans and society evolves. Should there be a catastrophe and there was no technology, then people would adapt and innovate. A case in point is during the recent ice storm and the lack of power that so many families endured. I read another article about a man who was creative enough to garner power from his electric car to run some basic electricity into his house. Although a complete and permanent meltdown of our society’s power grid would be completely catastrophic, through time, people would adapt.

How easy we would adapt is another issue though. As a “boomer”, it might be easier for myself to adapt when I have a history of an earlier (and much younger!) life where technology was not so prevalent. I can make change from a $5.00 bill, I know how to read a paper map and after adjustment, I’d be okay. My biggest fear would be that I’ve have to resort to pen and paper to write – either that, or find some nifty manual typewriter, reams of paper and plethora of liquid paper for the inevitable mistakes!

Each of us would probably have our own particular challenges but I wonder how difficult it would be for the younger generation when all that they know is technology. I can’t even begin to imagine how difficult an eFree society would be for those 35 years old and younger.

In this power-free scenario, the current infrastructure of our society would be devastated and completely useless. For some aspects, the chaos would be short term and in other respects it could take years and even decades to “rebuild”. Give it 25-30 years though, mankind – being as resilient as they are – would pick up the pieces and find alternative ways to run society.

Pity be the current generations should this occur though – the current blackouts in Toronto during the snowstorm has truly illustrated how dependent we are on this technology which remains – a double sided coin of the good and the bad.