Need Help-Get Random # of Range values from "Database Sheet"

**smalltime** · 08-06-2009, 03:50 AM

hello.i am new to this site.hope i can get some help

i want to improve my quiz template. this is my idea.
Please see attached file

1) a user will enter the # of questions he wants to answer.
2) after clicking "Enter", random questions will be generated in the "Quiz Sheet"
3) Questions will come from "Database (Questions) Sheet". ( I plan to place at least 100 questions to this database).

any idea? thanks in advance

**Palmetto** · 08-06-2009, 01:35 PM

See if the attached does what you need.

Briefly:

A random list of numbers is created using a User Defined Function to create a unique list of random numbers. See Intro Sheet cell S1
Cells on the Quiz sheet reference the random numbers and look up the question and possible answers
The Question Database has been reformatted to a true "classic" database structure and a Dynamic Named Range has been created for it.

Code for the UDF:

Please Login or Register  to view this content.

Code to put the numbers into a usable format

Please Login or Register  to view this content.

Last, a small macro to go to the Quiz Sheet

Please Login or Register  to view this content.

**smalltime** · 08-06-2009, 09:02 PM

Thanks palmetto.I will give it a try

**smalltime** · 08-06-2009, 11:23 PM

@ palmetto..thanks..your code is a big help, the output is my expected result

**Palmetto** · 08-07-2009, 07:27 AM

Glad to have been of help.

Bear in mind that as your question databaase grows, you will need to adjust the upper limit in the UDF (cell S1). It is currently set to a value of 100.

To alway keep the upper limit current to number of questions, in cells A1 on the Database sheet, use the COUNTA function to return the number of rows that have a question. Name this cell and use it in the UDF like so:

=RandLotto(1,QCount,E1)

Where QCount is the name of the cell (call it whatever you choose).

**smalltime** · 08-11-2009, 03:46 AM

Hello again Palmetto.

The quiz project is almost finish. I just stick to 200 questions.

I am applying protection in the sheets to avoid accidental erasures by user.

But when I apply protect sheet, the "Update_questions" macro would not run.

Any suggestions?

Thanks in Advance

**Palmetto** · 08-11-2009, 07:22 AM

But when I apply protect sheet, the "Update_questions" macro would not run.

See this link: How To Run Macros On Protected Worksheets

Also, by default all cells have their "locked property" set to locked so when you protect a worksheet all input cells (say the "answer cell" ) are also locked unless/until you change the cell property to "unlokced".

Turn off all protection, select the cells in which users will make an input, right-click and choose Format Cell, then pick the protection tab in the dialog and clear the check box next to "Locked".

Reapply sheet protection and make sure to adapt/use the code provided in the link above. Post back if you need more help.