I’m going to assume you can make files in Flash that can read from text files, but you don’t need to know anything about MS Excel. You could use any spreadsheet program such as OpenOffice Calc, but I’m going to talk about Excel for this article.
I teach English in
My quiz programs are simple and work from simple text files. One example is my ‘past tense’ quiz. I made a file with over 100 words (in Japanese) and then 4 possible spellings of the word in English, one of which being correct. Another example is ‘Many Much’. I have a list of about 250 things in English and students have to choose if they are countable or not and select ‘how many’ or ‘how much’. The latter requires 250 lines in a text file, with an ID, word, correct answer, wrong answer, even more if I gave an example sentence. I am far too lazy to type all of this out in the URL encoded format.
It’s just too much for me, plus some of the entries seem to repeat and others seem to progressively increase. For situations like this where the data is similar and increments in a pattern, Excel can save you hours. Let’s get started.
This project will be simple, but once you learn the concept you can create bigger files and databases. For this project we will have a text file with three variables, WORD, ANSWER, and WRONG. This will be for my quiz program that displays the word and gives two options where students select the correct answer. At the end I will show some other shortcuts that can be added to save even more time.
First you need to open your spreadsheet.
In column A, row 1 type the following:
Now skip over to column C row 1 (from here called C1 etc) and type:
Now skip over to column E1 and type:
To clarify, all you are doing is clicking once on the cell and then you start typing. At this point you should have this:
Now you want to click and drag from A1 to E1. They should all be highlight as shown below.
Now we get to the part where the spreadsheet comes in handy. Move your mouse so the cursor is over the little square of the bottom right corner of all the selected cells. You will notice it changes when you are directly over this little box. However, be sure you are over the little square, if you go over a line the cursor changes to something else.
If you are over a border line of the cells, the cursor changes to a 4 point arrow. That allows you to physically move the group of selected cells, which is not what we want to do. We want the little box in the lower right corner. Below I drew a red circle around it.
Click and drag it down about 20 rows. Then you will see the magic. If there is a number in a cell with some text, and you click-drag this box it will automatically increment. If there is only a number, it will simply copy the number. If there are two cells with two different numbers like 1,2 it will also increment those. Likewise, if the numbers are further apart such as 50, 100 the next number will be 150. This also works for sequential words such as January, February….Monday, Tuesday….
Once you have dragged this box down, you should see the following:
You can already see how easy and useful this can be. But it’s still not over, there are other things we can do that save even more time. Plus we have to concatenate them all together to make the file.
Now, to make use of the spreadsheet we need to group similar answers together. I am going to make this about “is it a verb or an adjective?” So the word will be either a verb or an adjective. I’ll have the first 10 as verbs and the last 10 as adjectives. I have to type in each one of these, but it won’t take that long.
Now back to the beauty of the spreadsheet
Type in the answer once in D1, and the wrong answer in F1. Then click our magic box and drag them down to row 10. Do this once each for both columns. You could also click drag all the boxes and hit CTRL-D which fills the rows down with whatever is in the top box. CTRL-D doesn’t increment, it simply copies. If there is nothing to increment, the little magic box will just copy also.
Now either reverse them and type the new correct answer D11 and vice versa for F11, or I would just copy and paste the group of cells from above into the opposite side as shown below. To copy the cells just click and drag and then hit CTRL-C or go to Edit-Copy.
The bluish highlighted area is the part I just pasted.
Ok, now we are getting really close, but we need to convert this into the url encoded style that Flash can read. Now we are getting into some heavier Excel stuff, but bear with me, it’s not so bad.
Go back up to the top and click on cell H1.
Now we are going to concatenate all these things into one space-less string.
In cell H1 type the following verbatim with no spaces:
Then hit return and you will see the first part of your string.
Now go back to H1 and double click to start retyping in the cell, or use the function bar at the top, and type the following (we are just adding this on to the end of A1&B1:
So it should completely look like this:
When you hit return, you will see the whole line as a Flash readable URL string.
Now comes my favorite part. Click cell H1 and then find the little magic box and drag it down to row 20.
BOOYA!! Excel just did everything for you.
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:
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.