Dynamically display in Active Cell value from 2 rows above it
 

Does anyone know the formula to ALWAY display the value in the active cell
which is 2 rows above it?
For example: Display in Active cell c13 the value in c11 (row13-2=row11).

The problem comes if I were to insert 4 rows after c11 and insert more data.
I would subsequently want to display in c17 (13+4) the value from c15 which
is 2 rows above it.

An absolute cell reference is not the same as saying a constant of 2 rows
above the active cell?

Any ideas to share??

Dynamically display in Active Cell value from 2 rows above it
 

On May 12, 10:34 am, Jocko_MacDuff116
wrote:
Does anyone know the formula to ALWAY display the value in the active cell
which is 2 rows above it?
For example: Display in Active cell c13 the value in c11 (row13-2=row11).

The problem comes if I were to insert 4 rows after c11 and insert more data.
I would subsequently want to display in c17 (13+4) the value from c15 which
is 2 rows above it.

An absolute cell reference is not the same as saying a constant of 2 rows
above the active cell?

Any ideas to share??

Hi Jacko,

Try...

=INDIRECT("C"&ROW()-2)

Ken Johnson

Dynamically display in Active Cell value from 2 rows above it
 

=INDIRECT(SUBSTITUTE((LEFT(ADDRESS(1,COLUMN()),3)) ,"$","") & ROW()-3)

--
Gary''s Student - gsnu200720

Dynamically display in Active Cell value from 2 rows above it
 

On May 12, 11:06 am, Ken Johnson wrote:
On May 12, 10:34 am, Jocko_MacDuff116

wrote:
Does anyone know the formula to ALWAY display the value in the active cell
which is 2 rows above it?
For example: Display in Active cell c13 the value in c11 (row13-2=row11).

The problem comes if I were to insert 4 rows after c11 and insert more data.
I would subsequently want to display in c17 (13+4) the value from c15 which
is 2 rows above it.

An absolute cell reference is not the same as saying a constant of 2 rows
above the active cell?

Any ideas to share??

Hi Jacko,

Try...

=INDIRECT("C"&ROW()-2)

Ken Johnson

Hi Jocko,

For any column, try...

=INDIRECT(ADDRESS(ROW()-2,COLUMN()))

Ken Johnson

Dynamically display in Active Cell value from 2 rows above it
 

A couple mo

In C13:
=OFFSET(C13,-2,0)
or
=INDIRECT("r[-2]c",FALSE)

=indirect() is a volatile function--it recalcs each time excel recalcs.

=offset() would be the one I used.

Jocko_MacDuff116 wrote:

Does anyone know the formula to ALWAY display the value in the active cell
which is 2 rows above it?
For example: Display in Active cell c13 the value in c11 (row13-2=row11).

The problem comes if I were to insert 4 rows after c11 and insert more data.
I would subsequently want to display in c17 (13+4) the value from c15 which
is 2 rows above it.

An absolute cell reference is not the same as saying a constant of 2 rows
above the active cell?

Any ideas to share??

--

Dave Peterson