I have a attached a sample file that includes cells (col. A) that I want to get a random cell from. The blank cell above each of the two ranges is what I want to use to extract a random cell from the cell range below it. The range is always different and I am trying to do something like what I have done in col. B but without having to:
1. I have to manually select each range (tedious when I have a full worksheet(s) of like data) 2. I have to calculate the number of rows (ie. the 169 in the example) so I don't grab from next groups range I would like to: IF there is an empty cell in a column, get a random cell of data from the range below it UNTIL the next empty cell. Any assistance is extremely appreciated. Regards. On Mon, May 4, 2009 at 7:20 AM, Satti Charvak <sat...@gmail.com> wrote: > Prepare en excel sheet and send it back...your usage of the formula is not > clear. > > On Mon, May 4, 2009 at 7:05 AM, Blank <blank.appa...@gmail.com> wrote: > >> >> I am trying to get a random selection of a cell containing text, >> basically: >> >> IF there is an empty cell in a column, get a random cell of data from >> the range below it UNTIL the next empty cell. >> >> I have achieved this manually with the following formula: >> >> =INDEX(A2:A171,RANDBETWEEN(1,169)) >> >> The issues that I have with this are: >> >> 1. I have to manually select each range (tedious when I have a full >> worksheet(s) of like data) >> 2. I have to calculate the number of rows (ie. the 169 in the >> example) so I don't grab from next groups range >> >> Does anyone know a better way to do this? >> >> Thanks for your assistance, >> >> Blank >> >> >> -- >> >> Thanks and regards, >> Satti Charvak >> ____________________ >> >> >> >> --~--~---------~--~----~------------~-------~--~----~ ------------------------------------------------------------------------------------- Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com ------------------------------------------------------------------------------------- -~----------~----~----~----~------~----~------~--~---
SAMPLE_RandomSelectionFromRange_GEN.xls
Description: MS-Excel spreadsheet
