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.

Technology as a Pacifier

In today’s hectic world, technology is a welcome relief from the constant bombardment of the daily life of work, travel, and home – repeat and recycle. Having a mechanism to give one’s self a break from the outside world can be a welcome and necessary relief.

To a certain extent I think that this is even more so necessary in today’s world where there is a technological connection for most people from virtually the moment we wake up until the time we go to bed.

There’s nothing inherently wrong with using technology as an escape, perhaps to listen to music, play a game or read something online but I often wonder if sometimes we’d be better off just totally unplugging ourselves, even for short periods of time.

I had a conversation with my girlfriend not too long ago and she mentioned how she had noticed so many children being handed iPads or other devices on the subway by a frustrated and weary parent. It caused me to think that she was right. As a parent, I can understand and appreciate the desperation we can have with a finicky child. However, I’m really wondering if using technology as a pacifier is such a good idea.

I do think back at earlier years though and I remember a time where the parent would be taking the child on their lap to read with them or read to them, or perhaps to invent and play a travel game with the child. Call me “old school” but I think that it’s not such a bad thing to have the parent being the source of relief for the child.

It’s probably not so different from when many of us were children and we were plunked down in front of the television – the electronic baby-sitter of the day. For the most part we turned out okay, but with today’s technology at every turn of our daily lives, the temptation to rely on the technology is difficult to resist.

What message do we send to our children when they are upset, frustrated or tired and we respond by handing them the electronic device instead of paying attention to them and spending that time with them? Is this the message that we want to give them, that the best way to deal with the stress is to reach for the device?

It’s a difficult road being a parent and I’m not naïve to think that I’m not guilty of the above. In some respects a parent will – and should – use whatever tools are at their disposal to help with parenting. I’ve had those days when it is absolutely necessary to steal an hour and keep one of my kids engaged with technology, but I’d like to think that it was just enough to allow me to regroup and recharge my batteries. A parent that selectively uses technology isn’t a bad parent; sometimes it will help them to become better parents as they can have that break in the day.

I suppose that I’d feel more comfortable with this technology if I saw a parent and a child at least sharing this technology together. I’d much rather see a mother and a son on the subway cuddled in together as they play some game or the child reads a story to the mother. At least then, the parent is there, in the moment and I think that’s what this article is really all about.

What3Words - A New Way to Share Directions

One of the biggest problems with the exchange of directions is nearly similar street names, or perhaps that the directions themselves are somewhat ambiguous "Meet me at the corner of King and Yonge" bring the most basic question - WHICH corner.

Well, a new website addresses all of this, in a very unique, creative and detailed manner. What3Words.COM has divided the entire world (well - probably the vast majority of it) into 57 trillion 3mx3m squares. What they have then done is assigned three words to this physical location and this combination of three words is unique to that specific location. The beauty of this is that this means that when I'm giving directions to the family cottage, I can just reference the three words associated with it - and a link to What3Words, and they will get the precise location.

The extra little tidbit of cool information is that you can purchase your own "one word" for that location on an annual basis - basically like a vanity plate for a car - or at least to make it easier to remember the What3Words key. I am seriously pondering "buying" the key for the cottage and using the name of the cottage as it's reference. It's a really unique way to communication a physical location!

Life Through a Lens

I was down at Harbourfront with my girlfriend as we watched the Canada Day celebrations. As we stood there watching the fireworks, the one thing that really struck me was how many people were watching the fireworks, but watching them through the lens of their camera.

Although they were indeed capturing the moments for posterity, it occurred to me that by focusing (pun only partly intended) on the filming, they weren't fully experiencing the event; they were only watching the fireworks through the rectangle of their device and therefore oblivious to the surroundings. For me, half of the fun was to experience not only the fireworks, but also the reaction of the people – especially seeing the joy of the toddler on his dad’s shoulders.

Upon further reflection, it seems to me that this isn't an isolated demonstration on how we can sometimes use technology as a “filter” in terms of our real world experiences. I watch people who think that it’s necessary to tweet or text message at a movie theatre. I don’t know how many times I’ve almost been clobbered as I walk down the streets and someone turns a corner, completely immersed on their cell phone, not aware of where they are going.

There are many more examples where life is experienced vicariously through an electronic device. The irony of this is that the true and full experience is easily obtained, just put down the device from time to time and look around!

Taking pictures or filming special moments help us to capture, reminisce and cherish those moments. Photography enhances our lives just as technology can. I’ll always like my tech toys and I’ve found things like my GPS or my smartphone (and the many Android apps that are installed on it) to be a wonderful resource the enables me to get directions, find restaurants, check account balances or even to check news or sports. I’d be pretty lost without technology, it’s an intrinsic part of my daily routine.

I think of this issue as if I were using a camera that had a macro lens and I was focusing in on a flower as I was photographing it. My field of vision would be greatly reduced and I would be missing out what was no longer visible in the periphery compared to a regular lens.

I’m not suggesting that people abandon their cameras – or their devices – to get the full experience. There’s something to be said for having a record of moments in time, that’s why many of us take pictures or videos. All that needs to be done is to stop and observe and experience from time to time.