Pick random cell in col of sheet regardless of length and ignore empty cells

[philjuk]

Hello all

I have posted here before and there were some really nice helpful people here, so I am back!

Ok, I have got this to work:

in RandCli sheet I have in F2 the number 1, then F3 the number 2 up to F73834 I have 73833. In the G col I have some random number in each cell starting from G2 to G73834 that I have got from another source.

On another sheet I have this:

=VLOOKUP(RANDBETWEEN(1,73833),RandCli!$F$2:RandCli!$G$73834,2)

Which basically picks one of the cells from G2 down at random.

This seems to work, although you guys probably think that is probably not a very smart way of doing it!

This works because I have a fixed number of cells so I can hard code the $F$2 and $G$73834

In similar way I have another sheet called TargetNum.

The B col in has some of the cells populated with values. The problem is I want the rand lookup to work where there might be ANY number of cells populated in the B col.

There maybe as few as 1 or as many as hundreds.

I got as far as this:

=VLOOKUP(RANDBETWEEN(1,COUNTA(TargetNum!$B$2:$B$65000)),TargetNum!$A$2:TargetNum!$B$11,2)

Where I counted how many populated cells were in the B col, so that part of the vlookup worked, but I had to hard code TargetNum!$B$11, cos for testing I had 10 cells populated.

I couldn't find a way to replace the 11 with the results of the counting calculation, like 1+COUNTA(TargetNum!$B$2:$B$65000)

I'm sorry if I don't explain my self very well!

Has anyone got any ideas, or is there a much better way of doing this full stop?

Thanks in advance!

[philjuk]

Sorry, I think I should have put this in the basics sub forum.

I don't know how to move it my self.