Random Selection

Happy New Year to you all.

I am pretty much useless at Excel and have MS Excel 2003 hence I am posting here. I have a golf game with loads of different courses to play with varying tournament conditions.

a) I have created a file of golf courses, and on sheet 8 is the full list of the courses in column B totaling 1956 names currently (this list will grow over a period of time). I am trying to make a random selection from these names to appear on sheet 1 every time I click a button. (I do know how to create a button and assign a macro to it lol)

b) I would also like on sheet 1 to show different random conditions so just by the click of one or two buttons I can see what course I am to play and with what conditions. The conditions are as follows :-

Conditions) No Wind : Breezy : Gusty : Windy
Greens) Soft : Moderate : Firm
Green Speed) Slow : Medium : Fast
Tees) Back : Middle : Forward : Juniors : Ladies

All help is greatly appreciated

Thanks In Advance

Steve

You could do this in a formula, then all you'd have to do is press F9 to recalculate to get a new result.

Assuming your list of courses starts at row 1 and that sheet 8 is called Sheet8 then on sheet 1 try:

=INDEX(Sheet8!B:B,RANDBETWEEN(1,COUNTA(Sheet8!B:B))) & ", conditions " & CHOOSE(RANDBETWEEN(1,4),"no wind","breezy","gusty","windy") & ", greens - " & CHOOSE(RANDBETWEEN(1,3),"soft","moderate","firm") & " and " & CHOOSE(RANDBETWEEN(1,3),"slow","medium","fast") & ", tees - " & CHOOSE(RANDBETWEEN(1,5),"back","middle","forward","ladies","juniors")

Andrew that formula is returning :- #NAME?

Sorry, I don't think Excel 2003 supports the RANDBETWEEN function. My mistake.

We could do it just using the RAND function, but it would make a long formula even longer. I'll knock up some macro code for you...

thanks so much......and all macro code help on what to do with it would be greatly appreciated

OK, here's some code - I've tried to make it well structured and readable, rather than short and snappy, but it should do everything you need.

Please Login or Register  to view this content.

thanks andrew...sounds like a dumb question what do I do with this

Originally Posted by StephenSmith

I do know how to create a button and assign a macro to it lol

Ah, I take it there's a "not" missing from that sentence then

I honestly can't remember how to insert buttons in Excel 2003, perhaps somebody else can help you with that, but what you could do is:

Press Alt-F11 to open the VB editor
From the "Insert" menu select "Module" (NOT class module)
Paste my code in, but change the first line to read something like "Sub GolfPick()"
Close the VB editor

In, I think, the tools menu there's an option to view macros. Click that and you'll get a list with one macro in, click on the options button and it will let you assign a shortcut key to run the macro. That should be it.

At the moment it is set to output to cell A1 on sheet 1, if you want the result in a different cell then change the line:

Please Login or Register  to view this content.

To the cell reference where you want it to output, e.g.

Please Login or Register  to view this content.

Andrew thanks so much that is very kind of you

Andrew that is absolutely brilliant many thanks works a treat...could I ask one more thing please

If I want the the course name to be displayed in cell D7 without the name course beforehand, the conditions displayed in cell E7, Greens in cell F7, Green Speed in G7 and the tees in H7

would you be so kind to change the code for me please

thank you

Of course, not a problem - glad it worked for you.

Please Login or Register  to view this content.

Brilliant Andrew thanks so much