Ryan McDonald

I have been teaching English in Japan, for 5 years. Before that I was living the life of Peter Gibbons from the movie Office Space. I've done some ColdFusion programming, but only recently got into Flash.

View all articles by Ryan McDonald

Now you’re done, but I’ll show you a few things to make the spreadsheet even more useful.

Let’s say you are making 10 tables that are all similar. I have about 10-15 for my quizzes. I could put all my eggs in one basket, but I like to separate things. I think it’s better to have 100 people using ten tables rather than everyone querying just one. All the tables have the same structure, but a varying number of rows. The first thing I do is take the table name out of the static part of the string and put it in cell E1. Then I change the string to reference E1 where the table name goes. That way I only have to change one cell when I copy it to a new sheet.

Change:

="INSERT INTO `verbORadj` VALUES

To this:

="INSERT INTO `"&E$1&"` VALUES

Notice the $1. That locks it in place so when you click and drag down it doesn’t automatically change it to E1, E2, E3. Since it would only change the numbers (which are row numbers) I put $1. If I had something that would change columns too, I could use $E$1. That means always refer to only this cell, don’t let the magic box change it.

Another thing I do is put IF statements in each cell and copy them down about 50-100 rows depending how many rows I expect to have. These IF statements basically say “If there is nothing in a certain cell, then don’t show anything, but if there is something, then run this function.” Finally I put the opposite answer IF statement in the wrong answer column and copy that down.

Once I do that, I only have to add new words and new correct answers, everything else automatically appears. This is where the functions start getting heavy because they are nested. If there is a mistake, you have to pick through each little mark to see. Excel doesn’t give nice error messages. Most often you’ll receive something like #NAME? Which basically means “ehhhh…..say what?”.

First thing I do is put this in the next ID line, here it’s A21:

=IF(B21<>"",A20+1,"")

If B21 is not equal to “” (nothing) then add 1 to A20, otherwise leave it blank. Test it by typing anything in B21, you should see “21” in A21.

Then I add the wrong answer IF function from the first part to D21:

=IF(C21="verb","adj","verb")

Easy enough. Except, if there is nothing in B21 then there will be nothing in C21 and it will be blank, which is not equal to verb. So cell D21 (and any below it) will show “verb”. Little annoying, but not a big problem. Let’s fix it anyway.

=IF(B21<>"",IF(C21="verb","adj","verb"),"")

IF B21 is not blank then check if C21 = verb, but it B21 is blank, this cell stays blank too.

Next you need to copy that whole SQL string and paste it into the TRUE part of an IF statement in F21. That sentence was in English and it is still confusing.

First I drag the magic box of F20 down one so it automatically updates everything. Then I cut that cell’s function and start with the IF function. If you type straight through it’s easy.

=IF(B21<>””,

Now just hit CTRL-V. Next don’t do anything other than type ,””).

If you cut the formula and then just paste it there it’s almost ready. The only thing you need to do now is take off the = sign from =”INSERT. The = only goes at the beginning of the function. So it should look like this, but with no spaces (for readability):

=IF(B21<>"",    "INSERT INTO `verbORadj` VALUES ("&A21&","&"'"&B21&"','"&C21&"','"&D21&"'"&");",    "")

IF B21 is not blank, then “INSERT…..);”, otherwise stay blank.

So now test it by typing a word in B21 and you will see all the other lines just pop up.

I usually make spreadsheets that automatically create the text file string and the database string at the same time. Then I add all these IF statements and copy-paste them down about 500 rows. Finally I just sit and type in about 10-20 different words and answers whenever I have free time or think of new things. After a few days I have these huge lists that are preformatted. I just copy-paste them into text files and/or databases. Hope you enjoyed this and I hope Excel saves you hours.