display text in a cell in a range of cells

[Shu of AZ]

I have a column of cells r1:r59. Each day all but one will be blank and will
contain text. That may be cell r6. The next day all but one will be blank
and will contain text but different. It may be cell r51. This changes daily
and the cell that may have the text in is not predetermined.

I need a formula that will display the text in the cell that is filled that
day and display it in another area of the sheet. B4.

Thanks in advance.

 

[Rick Rothstein]

Not sure if this is the most efficient formula or not, but it works...

=INDEX(R1:R59,SUMPRODUCT((R1:R59<>"")*ROW(R1:R59)))