I am trying to extract a random cell from a range listed below it. The pool differs for every item and no range size is the same with some being 110 columns down and others 80 and so forth.
This seems almost like a Edit>Go To>Special>Blanks Fill but more advanced so as to randomly call one of the cells below it from a range that is made up of all cells below it until the next blank cell (where each empty cell corresponds to a different item). I've tried using many random functions and some found macros but ultimately, I am stuck at self-defining the ranges so that my sample only comes from the correct range. There are literally hundreds of thousands of rows per worksheet but I only need to work one worksheet at a time as other calculations are quite resource heavy and I can generally work 25-30k rows without too many slow downs. My spreadsheet basically looks like this: <Empty Cell> image1-1Link.jpg image1-4Link.jpg image1-9Link.jpg image1-2Link.jpg image1-3Link.jpg image1-5Link.jpg image1-8Link.jpg <Empty Cell> image2-6Link.jpg image2-2Link.jpg image2-8Link.jpg image2-9Link.jpg image2-3Link.jpg <Empty Cell> I have used the function: =INDEX(C3:C142,RAND()*140+1) but this only works at extracting from the defined range and also requires a count. For a few items, this is fine, but for ongoing hundreds of thousands, it doesn't effectively work as there are too many errors in the extraction with all the different range sizes. Thanks for your assistance. - Steve --~--~---------~--~----~------------~-------~--~----~ Visit the blog to download Excel tutorials at http://www.excel-macros.blogspot.com To post to this group, send email to excel-macros@googlegroups.com For more options, visit this group at http://groups.google.com/group/excel-macros?hl=en Visit & Join Our Orkut Community at http://www.orkut.com/Community.aspx?cmm=22913620 Visit the blog to download Excel tutorials at http://www.excel-macros.blogspot.com To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com To see the Daily Excel Tips, Go to: http://exceldailytip.blogspot.com -~----------~----~----~----~------~----~------~--~---
