A basic ability of Excel is to be able to create a reference to the value or formula that is in a cell located on a different worksheet, or in a completely different spreadsheet file. If you are referring to a different spreadsheet (called a workbook by Excel) your reference is called a link. It's a lot easier than you would think. You can create either relative or absolute references (click here for more information) to other sheets. We'll cover referring to another sheet in the same workbook here.

On reviewing my log files I see that there are quite a few people coming in from various search engines looking for a way to split a name field into a first name and last name using Access. I know myself that this is a common issue, as many times this wasn't given much forethought when the table was originally created. Unfortunately, there is no really simple way to do this, at least that I've ever found. Fortunately, it can be done, albeit with usually less than perfect results. If you haven't ventured here before, consider this a welcome to the world of VBA (Visual Basic for Applications).

A reference is a way to point to a cell or range of cells in Excel. In effect what you are doing is telling Excel where to look for values to use in your formulas or to place in other cells. There are several different ways to do this, the most common being the absolute and relative references. A relative reference means that Excel will change the cell your formula is pointing to if you copy or expand the cell containing the formula. An absolute reference means that the referenced cell will not change when you change the position of the cell with the formula. You can refer to cells on the same sheet you are working on, to another sheet in the current workbook, or to a sheet in a different workbook. When you refer to a cell in another workbook you are creating a link to that workbook. But let's back up a little and give a couple of simple examples of relative and absolute references.

So last weekend E takes the kids to Borders for a little reward for being good (books are big in our house), and the little guy gets a book called Heckedy Peg, written by Audrey Wood, with very cool illustrations by Don Wood. The story is about a poor mother with seven children, named Monday, Tuesday, Wednesday, Thursday, Friday, Saturday and Sunday. She tells the children that because they have been good she will go to town and get them whatever they want. Each child has a special request, with Monday asking for bread, Tuesday asking for a pocket knife, Wednesday asking for a china pitcher, and so on. While the mother is away a witch named Heckedy Peg comes by and tricks the children into letting her in. She then turns the children into various types of food, and takes them to her hut in the woods for her own little twisted feast. On returning and finding her children missing the mother is understandably distraught, but a blackbird takes pity on the mother and leads her to the witch's house. Once there she has to guess which child was turned into which food, and get it right the first time or else! Well, I don't want to give up the ending so I'll stop there. But I'm sure at this point you're asking 'so what on earth does this have to do with sorting data in Excel?'. Well, I'll tell you...

Let me first say that there are many different ways to have an effective PC backup strategy. The important thing is to just have one. There are two reasons why you really need to have your computer backed up. One is to retrieve a file that you've lost, deleted, corrupted or changed (when you didn't want to). The other is to have a backup in case something happens to your hard drive or computer. The thing about any backup strategy is that you need to think about them carefully before you need them, and you really need to stick with them. In addition, you need to test them once in awhile, to make sure that you're actually saving the information you need, and that you can get it back if (when) you need it. I've tried and used many different strategies over the years, back to the days when you could back up on a floppy drive (we'll never see those days again!). It's really a moving target, as technology grinds inextricably forward so must your strategy. One thing I can guarantee is that when you do need your backup it will be completely unexpected and will come at the worst possible time. The sudden realization that your computer has gone 'belly up' and that all of your information is gone is truly gut-wrenching for someone that uses it regularly.

Every table you create in any database should have a field by which every record in that table can be uniquely identified against any other record in that same table. Say what!!??? OK, I just made that up, but when you read things about primary keys or indexes in databases that is the kind of tech-talk that makes people walk away without even trying. It's really not that complicated, trust me.

OK, so you've opened up Access a couple of times, clicked on a couple of things, and closed it right down again. It's really hard to know where to start, and reading the help files and manuals don't really seem to help much. Well, let's fix that. We'll start by creating a really simple table, which is where the 'rubber meets the road' when it comes to databases.

A typical question I run into is 'Why should I use Access when I can do everything I want in Excel?'. The answer is never black and white, but each of these programs have been created to address specific types of problems. I find many cases where people have used Excel for something where they really should have used Access. This usually happens because people are familiar with Excel, but not quite so comfortable with using Access. This can get you into trouble, and you really should ask yourself some questions before you get started. Here are some of the items that go through my mind when I'm trying to help someone decide which of these application should be used:

I've been developing database applications for years, and have worked with both large and small systems. A large percentage of my daily job revolves around working with the data from a fairly large custom Oracle database that drives all of the manufacturing and accounting processes for a mid-size US-based manufacturer. I also oversee the development and maintenance of a MySQL database that drives the corporate website for that same company. Previous to that I worked for several years as a consultant and software developer specializing in Access applications. I've noticed along the way that there are a lot of database administrators and developers that have a real bias against Access, and thought I would spend a minute or two chiming in with my own two cents.

So, I'm doing some work on my laptop the other night and E looks over my shoulder and says 'Hey, I've got a question for you. I was working in Word today and I must have typed the same word over and over 40 times. Isn't there a way to create a shortcut key or something where I can type in 2 or 3 letters and have the word fill in automagically?'. So I say 'Well, actually, yes, there is'.