Cell Referencing

[Kris W]

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

 

[Charabeuh]

Perhaps ?

This example:

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

 

[Charabeuh]

This is a array formula.

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

 

[Ashish Mathur]

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)),"",LOOKUP(REPT("z",99),F$16:F16))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Kris W]

I should clarify: I need to reference the nearest cell with text in the same
column above the current cell.

 

[Charabeuh]

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(LastAboveCells),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(LastAboveCells),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+Shit+Enter instead of Enter.

Does this help you ?