Hyperlink to Random Cell

**lake54** · 05-15-2009, 03:06 PM

It's me again:

I want 2 cells to display the following:

1: A hyperlink to a random cell in a particular column

2: A hyperlink to recalculate the spreadsheet (to generate another random hyperlink in the cell above, but obviously it need not be linked, literally to 'refresh').

Now, I can't find anything about number 2, but for number 1 I tried the following:

=HYPERLINK((ADDRESS(INT(RAND()*100),COLUMN(H:H))),"Random User")

But it keeps coming up with can't open file - obviously it's trying to open an external file. I've tried with quote marks around different bits, for example "ADDRESS.....H))"

Thanks once again for any help rendered guys.

Lake54

**NBVC** · 05-15-2009, 03:08 PM

For number 1: Change formula to:

=HYPERLINK(("#"&ADDRESS(INT(RAND()*100),COLUMN(H:H))),"Random User")

Not sure exactly what you mean with #2

**lake54** · 05-15-2009, 03:10 PM

Thanks, that worked great!

And I've just realised that Number two has now been made obsolete.

I'll mark as solved now.

**shg** · 05-15-2009, 03:17 PM

Sooner or later, int(rand()*100) will return zero, giving an invalid address.

=HYPERLINK("#R" & INT(RAND()*100 + 1) & "C8", "Random User")

**NBVC** · 05-15-2009, 03:25 PM

Since RAND() is already volatile... you can still use ADDRESS() function can't you?

=HYPERLINK(("#"&ADDRESS(INT(RAND()*100+1),COLUMN(H:H))),"Random User")

**lake54** · 05-15-2009, 03:53 PM

Sorry - only just noticed I had more replies!

Both work for me, but I've chosen shg's method, and changed 100 to 10,000 (without the comma) because the values were quite small (there's always going to be around 200,000 rows, and someone needs to be able to go to a random row at any time).

Thanks for your help once again :-)

**shg** · 05-15-2009, 04:11 PM

Apparently I had R1C1 on the brain:

=HYPERLINK("#H" & INT(RAND()*100) + 1, "Random User")

**lake54** · 05-15-2009, 04:17 PM

So should I be using the latest suggestion, shg?

**shg** · 05-15-2009, 04:19 PM

Any of the many are fine. Pick one that tickles your fancy.

**lake54** · 05-15-2009, 04:42 PM

OK thanks - I've kept with the one I said earlier as I now have 2 formats of the same spreadsheet, all zipped up and emailed out now lol.

I'm sure I'll be back in the future guys - this is really great for a forum!