Hello,

You don't clarify more. What do you mean with "to reference" ?
what do you want ?: the content of the cell, the address of the cell or
something else ?

I can suggest:

1) define a name: LastAboveCells that refers to:
=INDIRECT(ADDRESS(1,COLUMN()) &":" & ADDRESS(ROW()-1,COLUMN()))

then

2) in your current cell insert the array formula:
=INDEX(LastAboveCells,MAX(IF(ISTEXT(LastAboveCells ),ROW(LastAboveCells),0)))
this will gives the content of the nearest cell above the current cell that
contains text

or

3) in your current cell insert the array formula:
=ROW(INDEX(LastAboveCells,MAX(IF(ISTEXT(LastAboveC ells),ROW(LastAboveCells),0))))
this will gives the number of the line of the nearest cell above the current
cell that contains text

or

4) in your current cell insert the array formula:
=ADDRESS(ROW(INDEX(LastAboveCells,MAX(IF(ISTEXT(La stAboveCells),ROW(LastAboveCells),0)))),COLUMN())
this will gives the adresse of the nearest cell above the current cell that
contains text

Formula 2,3,4 are array formula. You must enter formula 2,3,4 with
Ctrl+****+Enter instead of Enter.

Does this help you ?



"Kris W" a écrit dans le message de
...
I should clarify: I need to reference the nearest cell with text in the
same
column above the current cell.

"Kris W" wrote:

Is there a way to reference the nearest cell above the current cell that
has
text - distance is always going to be variable.