Random word generator for learning

Hi Excel guro's

In my Japanese study I encounter many new words every day, which are written into an Excel document for easier practice.

So what I'm looking for, is a way (formula) to randomly select words from my study list, and then I have to fill in the meaning of that word - and then if possible a message will tell me if I'm correct or wrong.

I have already tried to find the solution in other threads, and the this one come nearest my particular problem: http://www.excelforum.com/excel-gene...generator.html. However, it's way too complicated for me, and I hope you guys can help

I used the code from the linked thread, and have added my Excel document.

Explanation of the Excel file:

Column A - English words
Column B - translation of the words in Japanese, written in Japanese characters (Hiragana/Katakana)
Column C - translation of the words in Japanese, written in Roman letters

F7 - The formula, which randomly select a word from Column A (A1-A10), and want me to write that selected word in E7. The formula also prompt the text "Try again", if I type in a wrong word.
NB: The problem with the text being prompted in same cell (F7), is that it replaces the show word, and I might forget what it is. So if the prompted text can't be show in another cell, then I rather not have it.

What I would like is:
F7 - randomly select a word from Column A (A1-A10), and want me to write the Japanese meaning of the word from column B (or C if the characters pose a problem).
But then instead of prompting the text "Try again" (if I type in a wrong word) in the same cell, I would like the text prompted in G7... But this might be too much to ask?

Example:
F7 - shows the word "dog"/A5
E7 - I type "みず"/B7 (or "mizu" if Japanese characters don't work with the formula), which is wrong
G7 - prompts the text "Try again"

F7 - still show the word "dog"/A5
E7 - I type "えぬ"/B5, which is correct
G7 - prompts the text "Correct"

and the process starts over...

It might be a lot to ask, but I hope you guys can help

Edit: I have many sheets with ex. Nouns, Verbs and so on in my "Study-Excel", and it would be awesome if I could use this function in it's own sheet, linking to the others. It that possible?

Knud

Nice!

However...

1. Can the "press F2 + Enter" be more easy, like just enter the text in E7? :P

2. Can I use the formula on ex. sheet 2 with reference to sheet 1?

3. Is it possible to use Japanese characters in E7 or only Romanji characters?

one more thing.

What if I want the formula to start from A3 and down?

Replace the a1 with a3 in the formula....and change the sheet name in formula ....
=INDEX(Sheet1!A3:A10,RANDBETWEEN(1,10))