I am not sure how much more of this you might need but I have been continuing to work on some English language resources. If they are not interesting or useful for you, just ignore them. I have had some positive feedback, however, so please humour me!
See ‘n’ Say
I created a Flash Card worksheet but I have called it See ‘n’ Say! What I have done is to take the list of words I created yesterday: the words that come from an earlier exercise. I turned that into a LOOKUP table and then created a new sheet to look like this:
Press the F9 key and the word in cell A4 changes RANDOMLY to another word in the list. The user only sees this worksheet and not the list or the formula,
=VLOOKUP(RANDBETWEEN(1,62),see_say_list!A2:B63,2,0).
Please note, because it is based on a random function, the same word might appear twice or even three times in succession … it does happen!
At the time of writing, there are just 62 words in the list. Expand the list as you go along and you will have a very powerful Flash Card, See ‘n’ Say resource.
Rearrange the Words
I haven’t automated any of this rearrange the words worksheet yet so what you see has all been done manually.
Take a sentence like this, the cat sat on the mat
Use Data Text to Columns, Delimited, Spaces to split the sentence into individual words in individual cells
Rearrange the words by dragging the cells around to give you something like this: on cat the sat mat the
As I was working with several sentences, I did all of this in one go, I did not do it one sentence at a time and I ended up with this, which I printed out for each student:
Crack the Code
As a boy I loved to crack codes so it should not be a surprise that I created a crack the code worksheet. All I did was to give a number to each letter in the alphabet: A = 1, B = 2 and so on.
Using another list of words, or even the same list I mentioned above, I split the words letter by letter by using a combination of the LEFT() and MID() functions, for example:
=LEFT(J18,1)
=MID($J18,M$17,1)
Then apple became a p p l e with each letter in its own cell
Then I used the following formula together with a LOOKUP range for the alphabet to change letters to numbers automatically:
=IFERROR(VLOOKUP(L18,$J$29:$K$54,2,0),””)
I then used CONCATENATE to recombine each word. For example
apple became a p p l e which became 1 16 16 12 5 … which I CONCATENATED into one cell like this:
=S18&” “&T18&” “&U18&” “&V18&” “&W18&” “&X18&” “&Y18
The complete worksheet looks like this:
Print that out for each student and they have no idea that all I do now, with any word up to seven letters long is to type a word in a cell and automatically Excel will use the code to generate the worksheet you see there. By the way, it would take only a short time to change this to allow for eight letter words, nine letter words … any number of letters in a word. I chose seven letters as my limit because I am dealing with beginners and not advanced learners.
You can even change the coding structure: not A = 1, B = 2 … but, for example, A = 6, B = 7 … or any other change you wish.
To prove a point, I have just spent one minute copying that worksheet and changing all of the words to give you these new codes, based on the same coding structure: A = 1, B = 2 … In this case, all of the words relate to bookkeeping and accounting:
Crack the Code Advanced
This is a crack the code and find the word exercise. In this case, I have changed the coding structure. A does not equal 1, B does not equal 2 … In this exercise, you have to find the values for A and B and so on and then find the words. This is not MI5 or CIA levels of coding, so think simple!
In this case, the words all relate to computers
Duncan Williamson
Excel with Excel Master 