I have what seems to be a pretty simple problem, but I don't know how to get arround it.
Basicaly I want to create a quiz type spreadsheet that will ask about a word in a language, and upon entering will check if the translation is good or not (kind of like those cards where you have a word in one language on one side and the translation on the other when you are teaching kids for example)
As a base I have 2 columns where the words and the appropriate translations are in (just 10 words for test purposes)
I used =INDIRECT("A"&RANDBETWEEN(1;10)) to get a random word from the list. (in the cell D9)
I type the translation into another cell, lets say it's D12.
I used =EXACT(D12;VLOOKUP(D9;A1:B7;2;0)) to compare what was typed in with the original translation in the table.
Problem is, that when I type in the translation into D12, upon hitting enter, excel recalculates the worksheet, so the value in D9 is not the one I was translating in the first place, thus the exact returns FALSE.
Is there a way to influence the recalculation process (except turning it off, which doesn't remove the problem). I want the value to stay fixed until it the check and only then change to a next one.
Howdy. Any time you use RAND or associated functions, XL will recalculate when completing any type of action, i.e. entering a value. So you will need a different function (don't think it will happen) or you will need to use VBA to get what you want, turning off Recalculation until you determine it is appropriate.
old, slow, and confused
but at least I'm inconsistent!