random text generator- urgent help

B

bazzdaciple

I am studying for a new course and i want to use excel to help me wit
my study. my objective is to create a button on sheet one. when i pres
this button it should come up with a word and display this word in
designated cell. this word has to be selected RANDOM from a list i
sheet two. everytime I press the button it should come up with a ne
word (totaly random). I also have to be able to update the list wit
new words. can somebody please help me with a formula or a VB scrip
??? please, this is very urgent.
thanx

osca
 
B

Bob Phillips

Oscar,

Here is some code to do it for the button

Private Sub CommandButton1_Click()
Range("A1").Value =
Evaluate("INDEX(Sheet3!A1:A10000,INT(RAND()*COUNTA(Sheet3!A:A))+1,1)")
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
G

Guest

Here's how you do it with no macro at all:

Write down the names in Sheet2 from Cell A1 below.

In Cell A1 in Sheet1, type the formula:

=OFFSET(Sheet2!A1,INT(RAND()*10),0)

Remember to change the number 3 in formula with the number
of total names (this is for 10 names).

Every time you press F9 (Excel re-calculates), a different
random name will appear in cell A1,Sheet1.
 
A

arno

Hi,

you could also use the function RANDBETWEEN(first, last) to create a random
number that matches to the rows where your words are. However, you need the
analysis-functions add-in. Read the excel help to "Randbetween" for details
and how to install the add-in.

=offset(shete2!a1, randbetween(1, 100)-1, 0)
would be the formula if you have 100 words starting at sheet2!a1.

arno
 
B

Bob Phillips

This fails on one of the OPs criteria, namely that he can dynamically add to
the list of words, so it should be changed to

=OFFSET(Sheet2!A1,INT(RAND()*COUNTA(Sheet2!A:A)),0)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Top