Randomizer skip empty cells

Hi there!

I'm new on this forum and I got a question. I made a randomizer and I would like to know how to skip empty cells. Right now, if a empty cell is selected it will result in a 0. In this case I would like to skip this cell and go on to the next till the randomizer finds a cell with a value. My formula right now is:

=INDEX($A$2:$A$1000;RANDBETWEEN(1;1000))

For some reason I can't upload a document..... -> uploaded an example

Thanks in advance

Will you please attach a SMALL sample Excel workbook(s) (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. (If trere are typical cases like: all unique values/duplicates could occur, day/night, nobody present/several persons at once, before/on/past due, etc. - please show them all or at least indicate in text) The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution(s) is/are also shown (mock up the results manually).

3. Make sure that all confidential/restricted information (either personal or business) like real e-mails, social security numbers, bank accounts, etc. is removed first!!

To attach an Excel file you have to do the following: Just before posting, scroll down and press Go Advanced button and then scroll down and press Manage Attachments link. Now follow the instructions at the top of that pop-up screen.

If example is representative (see point 1 in my previous post) and you just have numbers and then empty cells such formula will do:

Formula:

Please Login or Register  to view this content.

My doubts are as follows:
- is representative? (if you have empty cells between filled ones, this formula will not work)
- now you have choosing with repetitions. Some values appear more than once. In very rare cases, you could end up even with ALL values the same

Perfect. So the addendum just for those who will find this thread using search tools
If the colums with the data to be selected have empty cells between values to be selected, then:
use helper column (of course in real life you could have this helper column somewhere in not visible space, like in column ZZ, or even in a separate worksheet (could be hidden one). In B2:

Formula:

Please Login or Register  to view this content.

and copy down

if you need random selection without repetitions use in some cell (say D2):

Formula:

Please Login or Register  to view this content.

if you want random repetitions:

Formula:

Please Login or Register  to view this content.

And copy down