OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

How to get a random cells txt contents into another cell...?

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
rtj
Newbie
Newbie


Joined: 02 Sep 2004
Posts: 2
Location: UK

PostPosted: Thu Sep 02, 2004 6:36 pm    Post subject: How to get a random cells txt contents into another cell...? Reply with quote

Ok, bit of background info:
A5 to A10... each cell contains a word.
What I want to do is RANDOMLY take a cell between A5 and A10, copy that text, and paste it into another cell (say B10).
What would be the easiest way to do it?

Also how do i 'refresh' (for want of a better word) the page to randomly choose another cell between A5 and A10 to paste into B10?

Any help would be greatly appreciated.
I've been searching Google all day for the answer to the first question!

All the best,
RT
Back to top
View user's profile Send private message
richhill
OOo Advocate
OOo Advocate


Joined: 16 Jun 2004
Posts: 418
Location: Mesa, AZ

PostPosted: Thu Sep 02, 2004 8:40 pm    Post subject: Reply with quote

B10 =INDEX(A5:A10;INT(RAND()*5)+1)
_________________
OOo Calc tips: http://www.openofficetips.com


Last edited by richhill on Sun Sep 12, 2004 7:36 pm; edited 1 time in total
Back to top
View user's profile Send private message Visit poster's website
rtj
Newbie
Newbie


Joined: 02 Sep 2004
Posts: 2
Location: UK

PostPosted: Fri Sep 03, 2004 5:34 am    Post subject: Reply with quote

Hi richhill,
Thank you SO much for that formula, it works a treat. Very Happy

many thanks
RT
Back to top
View user's profile Send private message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Fri Sep 03, 2004 5:56 am    Post subject: Re: How to get a random cells txt contents into another cell Reply with quote

rtj wrote:
Ok, bit of background info:
A5 to A10... each cell contains a word.
What I want to do is RANDOMLY take a cell between A5 and A10, copy that text, and paste it into another cell (say B10).
What would be the easiest way to do it?

Also how do i 'refresh' (for want of a better word) the page to randomly choose another cell between A5 and A10 to paste into B10?

Any help would be greatly appreciated.
I've been searching Google all day for the answer to the first question!

All the best,
RT


You received a fine answer [much neater for your purpose], but it brought to mind another technique, so for what it's worth I thought I'd throw it in. It is useful if preparing something like random bingo cards.

Suppose you have a list in column A. In column B, opposite each, have the formula =RAND(). You can copy formulas by dragging of course. F9 will give fresh values any time. Then do a Data Sort using column B, and you get incidentally a random distribution of column A, which you can use anywhere else needed for that purpose.

David.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group