randon names

[Stevesmith]

Hi all

I am new here, and am desperate for some help:-

I am trying to create a worksheet which will random pick one of three texts in column A. I am using the formula :-

=INDEX(A1:A3,INT((RAND()*3)+1),1)

which is fine as it does what it says and selects one of the three texts (windy,breezy or no wind).

My problem being is that if I enter anything else in any blank cell the original random selection changes, in fact if i enter anything on any worksheet within the file the original will change and does so upon entry of anything in any cell.

I would like the random selection to remain static upon it's original selection until told to change via a button and assigning a macro.

All help on this will be much appreciated

Many Thanx

Steve

[mrice]

Hope this simple macro helps.

[Stevesmith]

mrice


that is a fantastic reply and many thanx for that - can you please tell me how you did that as I have another two sets of variables for green hardness and green speeds to include

thanks a million mate

steve

[Stevesmith]

hi mrice

if i gave you the variables could you please create another workbook (forever in your debt if you do)

Here we go then:-

Tees :- Back - Middle - Front

Weather :- Windy - Breezy - No Wind

Green Hardness :- Firm - Moderate - Soft

Green Speed :- Fast - Medium - Slow


as you can see this is golf related

many thanx for all your help

steve

[mrice]

You can see the code by hitting alt F11

Please Login or Register  to view this content.

There's a loop that deals with the columns in turn.

Within the loop...

• a random number 0-1 is generated
  this is multiplied by 3
  the integer portion is separated giving 0,1,2 with equal probability
  Two is added to give a row number
  the value in row 5 is set to the same as that for the above random row 2,3,4

Hope this helps

[Stevesmith]

Hi Mr Rice

many many thanx for that, so if I wanted to move the results (the random selections) say to cells AA5 and across (AA5:AB5:AC5:AD5) and produce 4 sets of random results so say 'TEES' in cells AA5 : AA6 : AA7 : AA8 along with weather, hardness and speed what would I have to put in the code please

Many Thanx

Steve

[mrice]

This is an additional loop.

Please Login or Register  to view this content.

[Stevesmith]

Hi Martin

Thanx once again for that, like your workbook in previous replies, could it be possible to create 4 sets of random(s) for each category, doesnt matter where in the sheet they appear, ie could start in A1

many thanx

steve