random text generator- urgent help

  • Thread starter Thread starter bazzdaciple
  • Start date Start date
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
 
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)
 
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.
 
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
 
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)
 
Back
Top