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 > > > > > --~--~---------~--~----~------------~-------~--~----~ ------------------------------------------------------------------------------------- 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.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
