Create random list then keep it unchanged

[RobertBobert]

Hi,
I'm using randbetween() to create a random list of ten numbers. Repeats are ok, so it's just randbetween(0,300) in every cell.

After I have created the list (and without using VBA) I want to keep that list as text. So I'd want it to generate the ten numbers but then somehow keep them as a list of numbers, and not change them every time the sheet recalculated.

With VBA I could do it by, say, having a button that copy-pasted as text and then deleted the cells containing the random function so it would not happen a second time.

The application (in case anyone has a better solution) is to randomly create a list of 10 questions for students from a bank of 300 potential questions (using index/match from a hidden sheet). The 300 potential questions and their answers are on a locked tab that the students can't see. I want them all to get the same workbook, then a random list is created for each student on a new tab. They answer the ten questions given to them and save the sheet. Then, when I open that sheet, I want to still be able to see the ten questions they were assigned and see their answers. Using randbetween() it "works", but every time they answer a question and click enter, the sheet recalculates and the ten questions all change.

(VBA is out because the IT department won't allow macro-enabled workbooks.)

The only workaround I can find is to ask the students to copy the list of numbers and paste-as-text. But this just opens the door to problems with students not following the instructions.
Thanks!

 

[footoo]

Here's a possibility :

Assumption : E2:E11 contains a formula that gets the 10 questions.

Do the following :
- In the instructions, explain that E2:E11 must be copied and pasted as text to display the questions.
- Conditionally Format E2:E11 with the formula =SUMPRODUCT(--ISFORMULA($E$2:$E$11))<>0 and format the Fill and the Font with the same colour. This will have effect of hiding the questions until they are converted to text.
- In addition, if you want, you could put a formula in a cell : =IF(SUMPRODUCT(--ISFORMULA($E$2:$E$11))<>0, "SEE INSTRUCTION 1",""). This would emphasize the instruction (the cell font could be, for example, red/bold).

The above, of course, will not prevent an Excel-savvy student from removing the Conditional Format and recalculating the sheet until he gets 10 questions with which he is comfortable, or merely clicking the Undo button to achieve the same.

 

[RobertBobert]

Thanks - definitely worth a try! No matter what a student will find a workaround - that's a given.