return random cell from set range from excel to power point

[Frances]

I'm making a Jeopardy! like game for my IT class. I want to click on,
say, "Software" for "200" button and have Power Point randomly get 1 of
20 questions listed in a column in Excel. I can link to a specific
cell, but can I link to a range of cells and have Excel return one at
random to the Power Point slide?

 

[vandenberg p]

Frances:

Here is one solution.

If you can link to a specfic cell then the following should solve your
problem.

In Excel assume you have your questions 1 per cell in A1:A20.

Then in b1 put this: =INT((20*RAND())+1)
This will generate a random digit between 1 and 20, each time the
sheet is recalculated.

Then in C1 put this: =INDIRECT("A"&B1)
This puts a cell address between A1 and A20 in C1

Then link to cell C1. You may need to add a "recalculate" to the code
to force Excel to recalculate each time you link to it. I did not
test whether Excel automatically recalculates when you
link to it each time.

Pieter Vandenberg

: I'm making a Jeopardy! like game for my IT class. I want to click on,
: say, "Software" for "200" button and have Power Point randomly get 1 of
: 20 questions listed in a column in Excel. I can link to a specific
: cell, but can I link to a range of cells and have Excel return one at
: random to the Power Point slide?

 

[Frances]

Mr Vandenberg

Many thanks, that goes a long way towards solving the issue. Could you
explain the additional "recalculate" step? Right now, if I manually
doubleclick B1, it recalculates and the new value appears in link in
the Power Point slide. But how do I get the link to trigger
recalculation before return the value in C1?

Frances

 

[vandenberg p]

Frances:

I'm sorry I have never tried to control Excel from another application.
So I can't really help you. I do know if you want to force Excel to
recalculate in a macro you simply issue the "Calculate" command. As
an example the following will recalculate the current workbook each
time it is executed in Excel.

Sub test()
Calculate
End Sub

What I do not know is how to excute this instruction from PP.
Your best bet would be to post the question to the Excel programming
group. You would probably get an answer there.

Pieter Vandenberg


: Mr Vandenberg

: Many thanks, that goes a long way towards solving the issue. Could you
: explain the additional "recalculate" step? Right now, if I manually
: doubleclick B1, it recalculates and the new value appears in link in
: the Power Point slide. But how do I get the link to trigger
: recalculation before return the value in C1?

: Frances

 

[vandenberg p]

Frances:

I have a different solution to your problem that will not require Excel.
You do (if I understand what you want) totally within PP. It does require
a 2 macros, but you can make the quiz as long or short as you want and
each question will appear just once. The quiz can be repeated.
Here is the link to an example file:

ftp://rohan.sdsu.edu/faculty/vandenberg/QuizShow.zip

Pieter Vandenberg



: Frances:

: I'm sorry I have never tried to control Excel from another application.
: So I can't really help you. I do know if you want to force Excel to
: recalculate in a macro you simply issue the "Calculate" command. As
: an example the following will recalculate the current workbook each
: time it is executed in Excel.

: Sub test()
: Calculate
: End Sub
:
: What I do not know is how to excute this instruction from PP.
: Your best bet would be to post the question to the Excel programming
: group. You would probably get an answer there.

: Pieter Vandenberg


: : Mr Vandenberg

: : Many thanks, that goes a long way towards solving the issue. Could you
: : explain the additional "recalculate" step? Right now, if I manually
: : doubleclick B1, it recalculates and the new value appears in link in
: : the Power Point slide. But how do I get the link to trigger
: : recalculation before return the value in C1?

: : Frances