Random word generator

Attach a small sample file.

Originally Posted by kvsrinivasamurthy

Attach a small sample file.

File uploaded. What I'm after is, if I enter "10" in B2 for the kup I'm taking on sheet one, (questions sheet), Excel will pick random words from the "10th kup" sheet from Korean list, and I have to type the English translation. If I get it right it says "Correct", and if I don't it will say "Wrong". When all the list has been used, I get a score at the bottom of right or wrong answers. If I change the kup, I get a different set of questions, based on what I enter into the "9th kup" sheet. Hope this makes sense. I just need it to basically test me on my Korean for each individual kup, depending on the number I enter on page one. Thank you for your reply.

No file ....

File added thank you

Take a look at the attached workbook and see if does the trick. You'll need to set calculations to manual or else the random word will keep switching. Because of the manual calculations, the only way to get an immediate response to your answer was to color-code it using conditional formatting. Put your 'Kup' number in B2, hit F9 to get a new word in A4, then type your answer in B4. If it's correct, B4 should turn green.

Note: your sheet for Kup 10 had an invisible space in the sheet name - " Kup 10" - that I fixed so it was consistent with the other sheet names

Originally Posted by cantosh

Take a look at the attached workbook and see if does the trick. You'll need to set calculations to manual or else the random word will keep switching. Because of the manual calculations, the only way to get an immediate response to your answer was to color-code it using conditional formatting. Put your 'Kup' number in B2, hit F9 to get a new word in A4, then type your answer in B4. If it's correct, B4 should turn green.
Note: your sheet for Kup 10 had an invisible space in the sheet name - " Kup 10" - that I fixed so it was consistent with the other sheet names

Yes that's almost what I am after. But is it possible to drop down a line each time until all the words are used up on page one, from all the words entered in the"kup" page?
Ie:- If there are 70 words in "kup 8", on page one it will drop down 70 times, and tell me that I got 36 correct, and 34 wrong on that particular grading.
That way I can see all the words that I am getting wrong and practice those more. I can also add a count cell at the bottom to show me how many I have got right or wrong. But thank you so far for your help.
Mark

Dropping down and selecting a new random word each time you enter an answer is better suited to a VBA solution. Using the RANDOM feature without VBA means that every cell with RANDOM or RANDBETWEEN will pick a new number each time you recalculate, so you would need to generate them all at once if you want them to remain unchanged so you can tally your score. If you want to stay VBA free, you can go with something like the attached, which posts all of the words from the selected Kup. You lose the randomization feature, but you can still select which Kup you want to work on and you can get a steady count of your right/wrong responses.

For future reference: Be careful about adding extra (unseen) spaces at the tail end of words. Some of your Korean answers contained trailing spaces, and when you're dealing with formulas, there's a huge difference between "Seth" and "Seth ", so the correct/incorrect formula in column C had to include a few extra complications to account for the discrepancies.

Another possibility is in the version below, which randomizes the list on the Kup page. For this version, you would go to the Kup page, use the filter in column B to sort by descending or ascending, which would randomly shuffle the words, then when you go to the questions page you'd get a random order. This might be better for studying. You would just need to build the other Kup sheets the way I've arranged Kup 10.

Originally Posted by cantosh

Another possibility is in the version below, which randomizes the list on the Kup page. For this version, you would go to the Kup page, use the filter in column B to sort by descending or ascending, which would randomly shuffle the words, then when you go to the questions page you'd get a random order. This might be better for studying. You would just need to build the other Kup sheets the way I've arranged Kup 10.

Yes that seems to have done it. Thank you so much for your help. This will help me to do my revision much easier, once I've typed in all the terminology. Again thank you

Glad I could help, good luck!

Thank you for your help. You'll be pleased to know I passed my belt, thanks to your help. the problem I have now is this....
I have started to enter the terminology for my 9th kup, but on line 16 of the questions page, the question of "step turn" is showing a #N/A error, even though the answer is correct in Korean..."Omgyo Didimyo Dolgi"... can you help with this please?

Also, would it be possible that every time I open the workbook, or enter a "kup" number in B2 on the questions page, that ALL of row B from 4 to 100 goes blank, removing any previous entries? This way I wouldn't have to manually clear all the answers on the Questions page from (B4:B100) to continue with the next kup.

Thank you for any help you can give. You helped me pass one belt...lets see if we can go all the way lol.
Regards... Mark

Congratulations! To assist with your N/A error, it would be helpful to see what you're working with. Can you post the current version of your workbook? The most likely culprit is a trailing or leading space in either your answer cell or the key page, but I can't tell until I can see it.

Auto-clearing a range upon changing B2 is easy, but requires a little VBA. Are you open to a VBA solution?

Not sure what vba is but will be willing to try. I have checked for spaces on the answer cell and key page, and there appeared to be none. I am trying to send you my new sheet, with the kup 9 error, but can't see where to upload it to you.

To attach a workbook, use 'Go Advanced', then click on 'Manage Attachments' and follow the cues to select your document and attach it to your post.

VBA (aka Macros) will let you encode procedures that normal Excel doesn't do on its own. Some offices/individuals don't like VBA solutions because malicious procedures can be written, but if it's for home use and you're comfortable with the code and/or you trust the source, it shouldn't be an issue. What you're requesting is a straightforward 'Change' event where we'll tell Excel to clear B4:B100 every time B2 is changed. If you can attach your workbook, I can put it in for you. Otherwise, I can try to walk you through how to install it yourself. In general, the latter option is safer, since it lets you view the code to determine that it's not malicious before adding it. For complicated procedures, it may be hard to tell what is/isn't malicious. The procedure we're adding for you, though, is pretty short and to the point; it looks like this:

Please Login or Register  to view this content.

Try uploading your workbook again, and let me know how you want to proceed with the VBA procedure.

Originally Posted by cantosh

To attach a workbook, use 'Go Advanced', then click on 'Manage Attachments' and follow the cues to select your document and attach it to your post.

VBA (aka Macros) will let you encode procedures that normal Excel doesn't do on its own. Some offices/individuals don't like VBA solutions because malicious procedures can be written, but if it's for home use and you're comfortable with the code and/or you trust the source, it shouldn't be an issue. What you're requesting is a straightforward 'Change' event where we'll tell Excel to clear B4:B100 every time B2 is changed. If you can attach your workbook, I can put it in for you. Otherwise, I can try to walk you through how to install it yourself. In general, the latter option is safer, since it lets you view the code to determine that it's not malicious before adding it. For complicated procedures, it may be hard to tell what is/isn't malicious. The procedure we're adding for you, though, is pretty short and to the point; it looks like this:

Please Login or Register  to view this content.

Try uploading your workbook again, and let me know how you want to proceed with the VBA procedure.

Yes I am happy for you to attach the code as I not experienced with it. I have added a new workbook also. (I think!)

Okay, try the attached. It should clear B4:B100 of the first sheet whenever you change B2 or open the workbook.

The 'Step turn' issue should be fixed, as well. "Step Turn " had a hidden trailing space on the Kup page, so when Excel tried to look up "Step Turn" from the Questions page, it didn't find an exact match.

You have been so helpful thank you. How do I add the code please? would it be possible for you to add the code, and send me the new sheet? If you need my email I will happily send. Like I said, I managed to pass my first belt thanks mainly to you... I made two mistakes in my Tul (Pattern), but because ALL my Korean was correct I made the points back up...that's thanks to your help. Thank you so much

Originally Posted by cantosh

Okay, try the attached. It should clear B4:B100 of the first sheet whenever you change B2 or open the workbook.

The 'Step turn' issue should be fixed, as well. "Step Turn " had a hidden trailing space on the Kup page, so when Excel tried to look up "Step Turn" from the Questions page, it didn't find an exact match.

Sorry I have just found your attached sheet. Yes that works perfectly. Just as I wanted it. Now I can fill in all the individual kups from ten to one. I may even ADD an extra page, where it has every page on just the one. To test ALL the Korean. For a black belt, you must know everything, so I think this would be of use.

I'm glad I could help! You should be able to add pages, though I'll note that the look-up function used to populate your list of words on the "questions" page looks for the number in B2 followed by "th Kup", so it actually won't work on the pages you currently have labeled "1st Kup" and "2nd Kup" unless you change the names to "1th Kup" and "2th Kup". Hopefully you're either well beyond those pages or you can forgive the poor grammar required to make them work. If you add a new page, just keep the naming convention - call it "11th Kup" or something similar.