how do I create a cell reference from ADDRESS function return?

[Coachdenny]

I am using the RAND function to generate a row number from which I want to
extract the contents of the cell at a specific column at that row. I have
used the ADDRESS function to create a text string that is the cell reference.
I am trying to use the CELL function with the "contents" info type but I
can't figure out how to use the cell reference I have created to direct the
function to the cell to get the contents.

=ROUND((RAND()*(COUNTIF(A:A, "<>"))-1),0) (places an integer number in cell
F2)
=ADDRESS(F2,1) (Returns the text string for the Cell in column A, row
defined as number generated and placed in F2)

=CELL("contents", ???) When I try to use the reference to the cell that the
text string is in from the ADDRESS function, I get the text string back.
When I nest Cell functions, I get an error.

 

[Bob Phillips]

Try

=CELL("contents",INDIRECT(F3))

 

[Shane Devenshire]

Hi,

If you want a random whole number for a cell address you don't need to

=ROUND((RAND()*(COUNTIF(A:A, "<>"))-1),0)

Why not

=RAND()*COUNTIF(A:A, "<>")+1

=RANDBETWEEN(1,COUNTA(A:A))

None of these formula guarentee unique results.

If this helps, please click the Yes botton

Cheers,
Shane Devenshire

 

[Rick Rothstein]

First off, your first formula (the one with RAND in it) can result in the
values 0 and -1 which would make bad row numbers. That aside, what would be
wrong with using this formula instead of the CELL one...

=INDEX(A:A,F2)

where you would use whatever column reference you wanted to look up the
value in?