Create random list then keep it unchanged

RobertBobert

New Member
Joined
Aug 19, 2014
Messages
4
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!
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,239
Office Version
  1. 2016
Platform
  1. Windows
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.
 
Last edited:

RobertBobert

New Member
Joined
Aug 19, 2014
Messages
4
Thanks - definitely worth a try! No matter what a student will find a workaround - that's a given.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,447
Messages
5,642,209
Members
417,262
Latest member
andrewd1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top