In one cell, I reference a cell in the next column to the left and one
row down

e.g., in cell Y182, I have:
if (Z183 = "No content liens","n","y")

But I often insert rows between rows 182 and 183. When I add a row,
the original contents of row 183 move down to row 184, but the formula
in cell Y182 also now references row 184.

I want the cell reference in Y182 to still refer to the immediately
adjacent row even after a new row is added (effectively I want that
reference to change to the "new" row - not the originally referenced
row).

Is that possible?

Julia Bell

try offset
=if(offset(y182,1,1)="No Content Liens","n","y")

"julia" wrote:

In one cell, I reference a cell in the next column to the left and one
row down

e.g., in cell Y182, I have:
if (Z183 = "No content liens","n","y")

But I often insert rows between rows 182 and 183. When I add a row,
the original contents of row 183 move down to row 184, but the formula
in cell Y182 also now references row 184.

I want the cell reference in Y182 to still refer to the immediately
adjacent row even after a new row is added (effectively I want that
reference to change to the "new" row - not the originally referenced
row).

Is that possible?

Julia Bell

=IF(OFFSET(Y182,1,1)="No content liens","n","y") entered in Y182

Will still refer to one row down and one column to the right(Z183) after rows
inserted.


Gord Dibben MS Excel MVP


On Sun, 20 Apr 2008 15:00:13 -0700 (PDT), julia
wrote:

In one cell, I reference a cell in the next column to the left and one
row down

e.g., in cell Y182, I have:
if (Z183 = "No content liens","n","y")

But I often insert rows between rows 182 and 183. When I add a row,
the original contents of row 183 move down to row 184, but the formula
in cell Y182 also now references row 184.

I want the cell reference in Y182 to still refer to the immediately
adjacent row even after a new row is added (effectively I want that
reference to change to the "new" row - not the originally referenced
row).

Is that possible?

Julia Bell

This will always refer to Z183:

=IF(INDIRECT("Z183") ="No content liens","n","y")
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"julia" wrote in message
...
In one cell, I reference a cell in the next column to the left and one
row down

e.g., in cell Y182, I have:
if (Z183 = "No content liens","n","y")

But I often insert rows between rows 182 and 183. When I add a row,
the original contents of row 183 move down to row 184, but the formula
in cell Y182 also now references row 184.

I want the cell reference in Y182 to still refer to the immediately
adjacent row even after a new row is added (effectively I want that
reference to change to the "new" row - not the originally referenced
row).

Is that possible?

Julia Bell