Category Archives: Blog

A Simple Trick for Referencing Data Between Worksheets In Microsoft Excel

Posted on by Posted in Pragmatic Programming Leave a comment

pastelinkI don’t work in Microsoft Excel often, and when I do, the documents tend to be pretty simple. Most of my work with Excel involves collating tabulated data and then publishing it a a Web-compatible format (the HTML <table> still has its uses).  I’ve never had any actual training in Excel (or any other spreadsheet software), but I’ve picked up enough to do the job at hand, and every now and again, I pick up a trick that makes working with data across Excel worksheets much easier.

One of the most tedious activities I’ve faced when working with Excel is to reference data between worksheets. I usually do this manually typing out the reference in the cell.  A typical cross-worksheet reference might look like this:

=’Another Worksheet’!$E$29

As you might have guessed from reading other posts in this blog, I have a tendency to commit typographical errors about every other sentence, so manually typing out a reference is a tricky proposition.

Fortunately, I picked up a new trick (and several others) for copying and pasting data references between sheets.

Rather than typing out the reference by hand, you can speed up your cross-worksheet functionality by using the following tip:

  1. Go to the sheet containing the data you want to carry over to the new sheet.
  2. Highlight the cell containing your target data.
  3. Press CTRL+C (or right click and select Copy).
  4. Go back to the sheet where you want a copy of the data to appear.
  5. Select the cell where you want to “paste” the data.
  6. Press ALT+E to bring up a floating dialogue that titled: “Office 20xx access key: ALT, E”.
  7. Press S to bring up the Paste Special menu.
  8. Press L to paste the link into the the cell.

The same task can also be accomplished (after coping the target to the clipboard) by highlighting the cell where you want referenced data to appear, clicking Edit, clicking Paste Special, and then clicking the Paste Link button.

I think the keyboard “hotkey” method is faster, but both methods are much faster than typing out the reference manually.

If you want to create references to a bulk of cells instead of copy-pasting all the values this is the option for you. Just use ALT+E S L to create an automatic reference to copied range of cells.

Troubleshooting As A Development Technique

Posted on by Posted in Blog, Pragmatic Programming Leave a comment

Pretty much all of my development activities start out with the question: “Can the website (or PC, or Smart Phone or toothbrush) do this form me?” My mind’s initial response is flood of brainstorming, ranging across choosing the best programming language and platform, which development philosophies to utilize, how this project fits into the organization’s business needs, how the project is funded, what is the ROI, and on, and on, and on.

To bring myself back in check, and to begin actually developing a solution, I’ve learned to simply rephrase the question as a a problem report.”Can the website (or PC, or Smart Phone or toothbrush) do this form me?” becomes: “When I do this to the website (computer, Smart Phone, etc.), it doesn’t do this.”

Rephrasing this “functional requirement” as a problem allows me to better identify the inputs the user intends to provide, and the outputs the user intends to receive.  Of course, there are usually many different inputs and outputs, but by framing to project in terms of a problem to troubleshoot, I can begin working the various input/output pairings (or clusters, as the case may prove to be).

The most simple an effective troubleshooting technique is isolate a problem into a series of events that should work, and then to work from each end, alternating every few steps, towards the middle of the series until the problem is found.

In the case of writing code, the first steps are pretty easy: I start with one expected input and one expected output, and then I create the necessary series in the middle to create the expected output when the input is provided. I progressively add inputs and outputs to the code, increasing the complexity until it gives all of the expected outputs based on the expected inputs (and doesn’t come crashing down when unexpected inputs are provided, but that’s a different article).  Each link in the development process will have its own problem cases that need to be resolved, but by working progressively to resolve each failure, the resulting code should be extremely robust and resistant to errors.

If you’re familiar with test-driven development, then this is probably starting to sound familiar. Test-driven development requires developers to create a test for a new feature before beginning development. Approaching coding as a troubleshooting process automatically requires the developer to create a test case by stating the failure state. From there, the coding is simply a matter of preventing the failure case from occurring.

Broken Background Images In Internet Explorer

Posted on by Posted in Blog, Pragmatic Programming Leave a comment

I should take it for granted that I’m going to have to resort to some sort of chicanery to get my CSS to work in Internet Explorer. Today, my background images weren’t appearing in certain elements on IE but were working beautifully on Chrome, Firefox, Safari, Opera, and every flavor of Webkit I could find.

Here’s my original CSS (yes, I’m working in WordPress again):

.widget-title {
backround-image: url(/wp-content/themes/mytheme/widget-header.jpg);
}

Easy, right?

Over the years, my hat for Internet Explorer has evolved into a warm, constant disdain. I’ve accepted it, accepted that I hate it, and accepted that while it may improve on some of the traits that irk me, it will introduce new traits for me to loathe.

Fortunately, this CSS background image problem as been around since about IE 5.5, so with a quick look on Google, I found a solution using filters. Here’s the new code:

.widget-title {
backround-image: url(/wp-content/themes/mytheme/widget-header.jpg);
filter: progid:DXImageTransform.Microsoft.AlphaImageLoader(
src='c',
sizingMethod='scale');
-ms-filter: "progid:DXImageTransform.Microsoft.AlphaImageLoader(
src='/wp-content/themes/mytheme/widget-header.jpg',
sizingMethod='scale')";
}

So, after only half an hour of banging my head on the wall, my CSS background images work.

Simple Way to Get a Domain from an Email Using T-SQL

Posted on by Posted in Blog, Pragmatic Programming Leave a comment

One of my recent projects required me to create a Microsoft SQL Server view that displayed (among other information) and email address and its underlying domain. Microsoft SQL Server and T-SQL provide a few simple functions that, when used together, make this a fairly easy task.

So here’s the T-SQL example (with the important stuff highlighted):

SELECT EMAIL,
RIGHT(EMAIL, LEN(EMAIL) - CHARINDEX('@', EMAIL)) AS 'DOMAIN' 
FROM MYTABLE

Parsing text inside a SQL Query can slow things down if you’re dealing with a large dataset, so use this code snippet with caution. If the application allows it, it’s generally a better idea to perform this kind of parsing in the application layer. ColdFusion, PHP and JavaScript provide comparable functions to T-SQL’s RIGHT, LEN and CHARINDEX.

If you’re feeling brave, you can also try pure RegEx rather than use native functions.

Episode I: The Phantom Mini

Posted on by Posted in Blog Leave a comment
ipad-mini-relative-screen-sizes

This from TechNewsDaily.com doesn’t show Amazon Kindle devices, but it still doesn’t help the iPad Mini’s case.

Over on PaperlessCockpit.com, I’ve been writing about my quest to find an iPad Mini. More than a month later, and I still haven’t been able to track one down in stores that I can actually purchase. Sure, the Apple Store will let me play with a demo unit, but unless I can run faster than the mall cop, I’m not leaving with one.

On Amazon, several online retailers say they have a few iPad Minis in stock, but they’re selling out quitckly. BestBuy.com will allow me to place an order (and take my money), but both the estimated shipping date and in-store pickup still show “Not Available.” The Apple online store claims the 16GB WiFi iPad Mini will ship two weeks from the time I order it.

But I’m not buying it.

I’m not buying into Apple’s promise of future joy. Nobody’s getting my hard earned ducats unless I get some good, old-fashioned, instant gratification.

On the positive side, Apple’s procrastination is has given me ample opportunity to compare other tablets that are actually in stores or available for immediate shipment. There are dozens and dozens of awesome Android tablets out there, and even Windows 8 tablets seem to rallying in time for the holiday spending spree.

My wife really loves the original Amazon Kindle Fire that I bought her last year. It didn’t have the hottest technical specifications on the market, but it’s proved itself to be a solid performer. Still maintaining the $200 price tag, but sporting improved technology, the Kindle Fire HD is a tempting holiday treat.

Apple seems to have a good handle on its App store, but I finally dumped iBooks last year due its content being locked in iOS devices. I want to read my books on any device I happen to get my hands on, and Amazon has been smart to make  the Kindle eBook Store available on any device. Amazon Prime Instant Video also isn’t locked into an Amazon-only ecosystem: I can watch rented, purchased or streaming movies on the Kindle Fire, my iPhone, my iPad, my MacBook Pro, or Xbox 360.

Apple, do you  have a bad feeling about this?

You should.