46

I've been on a real rollover kick lately. I'm really trying to figure out if it can be useful. Earlier today I started making a Periodic Table of Elements using Excel. I employed the rollover technique to allow the user to gain information about an element simply by rolling over a cell. Well, for some reason, I couldn't stop there. So what was meant to be a small project ballooned into something larger. Unfortunately, my sticking to good coding practice didn't keep up with craving to do more. So what I present to you below isn't really a polished product. If you poke through the named ranges and the rollover indexes, you'll probably see that I add and subtract one to them somewhat randomly (a cheap trick - this is  due to my trying to reconcile the table copied from Wikipedia with my indices).

As you can see below, you can not only gain information about an element but you can also toggle on and off different element groupings.



If you want to "crack" the file, the first thing you'll need to do is reset the ScrollArea (Click on a cell. Go to the Developer tab, click Properties. Delete the reference in the ScrollArea box.). Then just unhide everything.

Good luck.
Periodic Table.xlsm

Update -
Reader Dario found an error in the spreadsheet (see the comments) - this is the result of some carelessness and cheap tricks on my part. An updated version will be released tonight. In the meantime however, you can still poke around the file :).

Another Update -
I've since fixed the bug described in the reader comments. If you find anything else, let me know!

Author: Unknown Labels: Advanced Excel, Data Visualizations, Dynamic Charts, Formulas, Interactive Dashboards, Rollovers, Spreadsheet Tricks, VBA

8

In my last post I used my patented rollover technique to create an effect similar to the one shown below:



Neat, huh? When you place your mouse over a cell, it changes color to show that you are selecting it. It's a true "rollover": no mouse-clicks required. In my last post, I show how that might be useful.

Wondering how I did it? Checkout this file:
Rollover Surprise.xlsm

Author: Unknown Labels: Advanced Excel, Excel Tips, VBA

2

Before we start, you might have noticed some renovations on this blog. Yeah, it was time for a change. I'm working this template out, so sorry if some of the formats seem off. Also, I have started a new facebook page. I'll be keeping it updated with stories from my favorite blogs. Also, you can post questions and stuff for me (or others) to take a crack at. Plus, you'll see that I'm just a normal average guy. So if you have a facebook, you should join. You really don't have a good excuse why not! :)

DOOOOOOOOOOOOO IT!
Option Explicit VBA | Promote Your Page Too


***

In early last year, this humble blog had very little traffic. All of that changed in April of last year when I posted an exciting discovery: the Rollover technique. This technique was written about by the wonderful Purna of Chandoo.org. At the time, I was super excited about all of the possibilities of this technique. Now, just about a year later, I'm unsure whether anyone (besides me) has really ever used it. I even tried to incorporate it into my last project, but I found my clients were all too used to clicking on a cell rather than rolling over one. In the end I removed the rollover and replaced it with a click. Perhaps the rollover technique is destined to be a cool gimmick and not much more.

But I'm not giving up. Reader Bert van Zandbergen has been sending me many fine spreadsheets featuring this technique. His enthusiasm is encouraging. So I give you a rollover you can use for your Gantt Charts!


Download the fun, here:
Gantt Chart Surprise.xlsm

To see my intermediate table, scroll to the right of the dashboard. Sometime in the short future, I'll post a how to make the highlighted cell stand out with a border. In the mean time, remember to go to Facebook and like this page. Seriously, what are you waiting for? 

Author: Unknown Labels: Advanced Excel, Dashboards, Interactive Dashboards, VBA

0

Today, I found this journal article, The role of OR [Operations Research] specialists in ‘do it yourself’ spreadsheet development, from the European Journal of Operational Research, by John S Edwards, Paul N Finlay, John M Wilson. The article outlines 20 Guidelines for Spreadsheet Development and 21 Best Practices Points, which I excerpt below.

Guidelines for Spreadsheet Development

Fig. 2. Guidelines for spreadsheet development.

Best Practices for Spreadsheet Development

Fig. 3. Best Practices Points. 

My favorite point is #14 from the list of Best Practices: 

Never input the same piece of data more than once.

This rule reminds me of the 17th rule of composition from the Elements of Style: "Omit needless words." Perhaps Best Practice-point #14 can be rewritten as "Omit needless data." 

_____

Figure 2 and Figure 3, from John S Edwards, Paul N Finlay, John M Wilson, The role of OR specialists in ‘do it yourself’ spreadsheet development, European Journal of Operational Research, Volume 127, Issue 1, 16 November 2000, Pages 14-27, ISSN 0377-2217, 10.1016/S0377-2217(99)00331-8.

Author: Unknown Labels: Advanced Excel, Excel Development, Excel Standards, Excel Tips

1

First, a big thank you to everyone following my blog. Here's some stuff I'd like to share in no particular order.

Stephen Colbert Come to My Wedding

We (my fiance and I) want Stephen Colbert to come to our wedding, which is in October of this year. My beautiful and wonderful fiance explains as follows:

During the tedious processes of inviting people to our wedding, my fiance and I began a running joke of inviting Stephen Colbert. Well, he may still be joking, but I'm not.

We don't have terminal illnesses or a terribly romantic love story--unless you consider our burning desire to have Stephen Colbert at our wedding an illness and online dating terribly romantic.

Want to help? Of course you do! Do this:

(1) Like us on Facebook.
(2) Follow us on twitter
(3) Get your friends to do the same!

Thanks in advance for the help, internet - you're so awesome!

* * *

Excel Custom Formats

Lately, I've really been into Excel custom formatting. This is my favorite page to reference when I have questions about custom formatting: http://www.ozgrid.com/Excel/CustomFormats.htm

Make to checkout all the other info ozgrid has to offer!

Looking for a cool Excel forum?

I am a contributor to the Excel forum, Excel Heros on LinkedIn. This forum is associated with the terrific Excel Hero website.

Visualization and You

I have a lot of strong feelings about visualizations as you may have noticed in a previous blog post. I believe that many organizations, including the media, software companies, and even universities are encouraging meaningless and distorted data representations.

The visualization guru, Stephen Few, has this excellent quiz to test your Graph Design IQ. Check it out!

Right now, I'm reading Few's book, Now you see it, which I highly recommend. A link for it will be up later on an "excel resources" page when it's complete.

Want to hire an Excel consultant, but don't know where to begin?

This page from Daily Dose of Excel is a good place to start.

Let's join forces!

As you may have noticed, sometimes my blog updates are sparse. I do this in my spare time and sometimes I'm just too busy to update. Part of the problem is I start writing tutorials that always become too big. For example, I wanted my sensitivity analysis tutorial to be complete in one post -- already, it might grow to three or four! I have a real problem.

Do you like writing? More specifically, do you like writing on Excel? Want to join forces? I would really love to have some more regular posts. Drop me a line if you're interested!

Have just one post you think you might want to share? Again, drop me a line!

I'd love to hear from you

Comment below, email, facebook, twitter, LinkedIn... whatever you want. Have something interesting? Share it!

Author: Unknown Labels: Data Visualizations, Excel Resources, VBA