Yes, but I have multiple ranges in the worksheet (250+) with each range having a different number of rows. If I use the index method: =INDEX(A2:A171,RANDBETWEEN(1,169)), then I am forced to select the range AND also to count how many total cells are in the range.
What I need to do is to do this by taking into account the blank cell between the ranges and automatically use the range only until the next blank so all cells in the range will be used for a true random cell of the entire range. Any ideas? I had attached a copy of a sample file but it doesn't appear to have shown up in this thread? Thanks. On Mon, May 4, 2009 at 5:08 PM, Fabio Lemos <flnle...@gmail.com> wrote: > 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 > > > > > > --~--~---------~--~----~------------~-------~--~----~ ------------------------------------------------------------------------------------- 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 ------------------------------------------------------------------------------------- -~----------~----~----~----~------~----~------~--~---
