Cell Referencing
 

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

Cell Referencing
 

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.

Cell Referencing
 

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.

Cell Referencing
 

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.

Cell Referencing
 

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.

Cell Referencing
 

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.