Change a number, then move the correct answer

[jm594]

I attached a spreadsheet with quiz questions in it. Column D is the Question and columns E through H hold the possible answers. Column I shows the correct answer for that particular question. Therefore, if the CorrectAns column is 3 then the correct answer is in the Ans3 column. If the CorrectAns column is 1 then the correct answer is in the Ans1 column. I want to randomize better the numbers in the CorrectAns column. Many times that column up with too many 1's, not enough 2's, etc. HOWEVER, If I change a number in the CorrectAns column then the physical location of the correct answer must change.

For example, if I change cell I2 to a 2, then the correct answer must move from G2 to F2 and an incorrect answer must them move into cell G2. Is there any way to accomplish this?

[AliGW]

Please continue in the original thread here: https://www.excelforum.com/excel-pro...er-change.html

Thread closed.