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

Perhaps ?

This example:

Put the formula in E11
=INDEX(E1:E10,MAX(IF(ISTEXT(E1:E10),ROW(E1:E10),0) ))


"Kris W" a écrit dans le message de
...
Is there a way to reference the nearest cell above the current cell that
has
text - distance is always going to be variable.

This is a array formula.

You must enter it with Ctrl+****+Enter
instead of Enter


"Charabeuh" a écrit dans le message de
...
Perhaps ?

This example:

Put the formula in E11
=INDEX(E1:E10,MAX(IF(ISTEXT(E1:E10),ROW(E1:E10),0) ))


"Kris W" a écrit dans le message de
...
Is there a way to reference the nearest cell above the current cell that
has
text - distance is always going to be variable.

Hi,

Suppose the data is in range F17:F22. In cell G17, use the following
formula and copy down

=IF(ISERROR(LOOKUP(REPT("z",99),F$16:F16)),"",LOOK UP(REPT("z",99),F$16:F16))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Kris W" wrote in message
...
Is there a way to reference the nearest cell above the current cell that
has
text - distance is always going to be variable.

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.

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.