Ok. That's possible too...
There is a named range (a value in this case) that put a limit on each block size.... if you set it to a very large number the calculation will last longer.... (varGroupSize that is set to 100). If you need to increase it you can. Regards! 2009/5/5 Michael Fowler <blank.appa...@gmail.com> > Thanks Fabio. > > But, this doesn't take into account only the range UNTIL the next blank. I > have made a sheet from your that shows three groups of data. The cell > calling the random data is taking into account ALL of the data. Can this be > limited to only each group and only to the next blank cell (after the last > data cell of ea. range)? > > Thanks for your help. > > On Mon, May 4, 2009 at 5:58 PM, Fabio Lemos <flnle...@gmail.com> wrote: > >> The formula was wrong, see this new file. >> >> 2009/5/4 Fabio Lemos <flnle...@gmail.com> >> >> you could create a variable range name and use it in your formula... >>> >>> 2009/5/4 Satti Charvak <sat...@gmail.com> >>> >>> 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 >>>>> ____________________ >>>>> >>>>> >>>>> >>> >>> >>> -- >>> Fabio L Lemos >>> >>> email: flnle...@gmail.com >>> >>> >> >> >> -- >> Fabio L Lemos >> >> email: flnle...@gmail.com >> >> >> >> > > > > -- Fabio L Lemos email: flnle...@gmail.com --~--~---------~--~----~------------~-------~--~----~ ------------------------------------------------------------------------------------- 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 ------------------------------------------------------------------------------------- -~----------~----~----~----~------~----~------~--~---
Book2 (1).xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet