MS Access MVP Crystal Long has a Random name selection workbook that was originally developed in MS Access. Today, in Episode #1531, Bill demonstrates the 'Awesome!' Random Selection workbook that Crystal has developed. Note that the very same workbook is available for download! See the Podcast for details!
Download Crystal's Workbook Here: http://www.mrexcel.com/podcasts_2011_2012/Random_Picker_by_Crystal_120318__XLS.zip
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Download Crystal's Workbook Here: http://www.mrexcel.com/podcasts_2011_2012/Random_Picker_by_Crystal_120318__XLS.zip
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
MrExcel Podcast is sponsored by Easy-XL.
Learn Excel from MrExcel Podcast, Episode 1531: Random Chooser Hey, welcome back to the MrExcel netcast, I’m Bill Jelen.
I was up in Seattle at the MVP Summit at the end of February.
And the cool thing there is you get to run into a lot of your old MVP friends.
One of the Access MVPs is Crystal Long.
Crystal said, “Hey, I sent you this workbook last summer to share on the podcast and you were too busy to get to it.” So she nicely sent it again.
Crystal is- originally wrote this in Access but then did it in Excel as well.
The way this works is you can put any kind of list anywhere.
In this case, there's a list of names starting in C8 and you need to randomly choose five of those.
Now, I would do this with =INDEX of that list and then Randbetween 1 and how many we have.
But the problem is, that’s the formula and of course, you get- every time you do anything to the spreadsheet, or press F9 or enter anything, those names change.
And so Crystal gives us a way to randomly choose some number, alright.
So we'll do all, okay, like she says here.
Show_RandomPicker, we will Run that.
And let’s say that we want 5 items and starts in cell B1 and goes vertically.
And the first cell of data is right here in C8, and click OK.
And there's my random list of 5.
And I can keep doing more random list of 5 over and over and over.
And what's nice is they are – see like these were formulas, right?
But it gives us the actual values up there and so, you know, that list is going to stay static.
Of course now any time, you know, you get a little utility like this, the first thing I always do is I do Alt+F11 to go look at the code and we start out with just one line of code.
From RandomPicker.Show, alright, so here’s our form to figure out where the good stuff is.
I go right to the OK button and double-click because that’s where things happen.
And- So she’s doing some validation up here and as I go down, I noticed that this is an XLS file in Excel 2003 so she’s not sure that she actually has access to Randbetween so it looks like the RowPick, she’s using the Integer of last row minus first row plus 1 times Rnd.
Rnd is the VBA equivalent of the RAN function, returns the number between 0 and 1 plus first row.
So that's how it's working, the VBA to do what I would do with the formula, so.
Hey, by the way, if you ever need Access Consulting, Crystal Long.
Just search for Access MVP Crystal and she'll come right up.
She does remote consulting.
So, a tip of the hat to Crystal for sending this on to us, for all of our Excel people.
Put a- LearnMrExcel.wordpress.com, we’ll have a link to this so you can download it and use it yourself.
Okay well, hey, I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel Podcast, Episode 1531: Random Chooser Hey, welcome back to the MrExcel netcast, I’m Bill Jelen.
I was up in Seattle at the MVP Summit at the end of February.
And the cool thing there is you get to run into a lot of your old MVP friends.
One of the Access MVPs is Crystal Long.
Crystal said, “Hey, I sent you this workbook last summer to share on the podcast and you were too busy to get to it.” So she nicely sent it again.
Crystal is- originally wrote this in Access but then did it in Excel as well.
The way this works is you can put any kind of list anywhere.
In this case, there's a list of names starting in C8 and you need to randomly choose five of those.
Now, I would do this with =INDEX of that list and then Randbetween 1 and how many we have.
But the problem is, that’s the formula and of course, you get- every time you do anything to the spreadsheet, or press F9 or enter anything, those names change.
And so Crystal gives us a way to randomly choose some number, alright.
So we'll do all, okay, like she says here.
Show_RandomPicker, we will Run that.
And let’s say that we want 5 items and starts in cell B1 and goes vertically.
And the first cell of data is right here in C8, and click OK.
And there's my random list of 5.
And I can keep doing more random list of 5 over and over and over.
And what's nice is they are – see like these were formulas, right?
But it gives us the actual values up there and so, you know, that list is going to stay static.
Of course now any time, you know, you get a little utility like this, the first thing I always do is I do Alt+F11 to go look at the code and we start out with just one line of code.
From RandomPicker.Show, alright, so here’s our form to figure out where the good stuff is.
I go right to the OK button and double-click because that’s where things happen.
And- So she’s doing some validation up here and as I go down, I noticed that this is an XLS file in Excel 2003 so she’s not sure that she actually has access to Randbetween so it looks like the RowPick, she’s using the Integer of last row minus first row plus 1 times Rnd.
Rnd is the VBA equivalent of the RAN function, returns the number between 0 and 1 plus first row.
So that's how it's working, the VBA to do what I would do with the formula, so.
Hey, by the way, if you ever need Access Consulting, Crystal Long.
Just search for Access MVP Crystal and she'll come right up.
She does remote consulting.
So, a tip of the hat to Crystal for sending this on to us, for all of our Excel people.
Put a- LearnMrExcel.wordpress.com, we’ll have a link to this so you can download it and use it yourself.
Okay well, hey, I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
