Too sophisticated. The formula below will do the same.
=OFFSET($A$1;RANDBETWEEN(1;70);0)
And the weak point is also same - when you put 10 such formulas into some
range, then how to avoid the that some value is selected twice or trice?
When this doesn't matter, the there you go!
The sort method doesn't yield duplicates. Since yours does, it doesn't
address the OP's problem!
--
Regards,
Tushar Mehta
MS MVP Excel 2000-2003 www.tushar-mehta.com
Excel, PowerPoint, and VBA tutorials and add-ins
Custom Productivity Solutions leveraging MS Office
[This followup was posted to microsoft.public.excel.programming and a
copy was sent to the cited author.]
I assume you want unique selections. In addition to the sort method
suggested, check the Excel | Tutorials | 'Random Selection' page of my
web site.
--
Regards,
Tushar Mehta
MS MVP Excel 2000-2003 www.tushar-mehta.com
Excel, PowerPoint, and VBA tutorials and add-ins
Custom Productivity Solutions leveraging MS Office
As I found out, it works with my formula too, but you have to sort only my
10 formulas. And they can be anywhere on sheet, or even on another
worksheet.
Btw, I recorded a little macro for sorting formulas, and attached a shortcut
to it - it's much handier this way )
There was an error - the formula must be
=OFFSET($A$1,RANDBETWEEN(0,69),0)
Otherwise the value in A1 is never selected, and sometimes 0 is selected
(when in A71 is nothing).
Arvi Laanemets
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.