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

You simply click-drag the cells in column H and copy them into your text file. Each line has the &, the variable name, the =, and no spaces (apart from those in a variable such as &word=a dog).

 

Can we make things even easier?

 

Oh yes, we can. Let's say you have a file with 500 entries and you aren't able to put them in order like we did above, or you want to add to the files and when you add data it won't be in order either. For these situations, or just to make it easier, we would use some Excel functions, mainly the IF function.

 

In this case, in cell F1 rather than typing the wrong answer I would write an IF function that would see what is in the correct answer cell and insert the opposite. The syntax for that is:

 

= IF (condition, true action, false action)

 

So in F1, I typed the following:

 

=IF(D1=”verb”,”adj”,”verb”)



This means IF whatever is in D1 equals “verb” (the quotes make it a string), then show (echo) the string “adj”. Otherwise show the string “verb”. The quotes are important, without them you get an error. Since the string text D1 does equal “verb”, then the true action is to show the string text adj. Since the only things that will be in D1 are “verb” or “adj”, the false action only has to show “verb”. You can nest these IF functions pretty deep, but I'm not going to cover that.

 

So now just drag the magic box down in column F and it auto-fills all the cells with the new function. Note that when another cell, such as the function in the H column cells, refers to a cell with a function in it, like the IF in the F column, it refers to the result of the function. Basically, whatever ends up in the "IF(D1=" cell is what the H colum cell will use. Now all you have to do is add words and the correct answer and it will auto-update the wrong answer.

 

This can also be used for databases in situations where large amounts of the data repeat or increment-decrement. However, for use in databases, this requires a bit more on the Excel side. I'll cover that in part 2 as well as show a few other little Excel tricks.  I've also realized recently that Excel can be used to automatically make RPG map systems, since they are usually repeating 1, 0, [], and commas. I'll work on that for part 3.


See the attached spreadsheet if you want a closer look.