change referenced cell after adding row

[julia]

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

 

[Don]

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

 

[Gord Dibben]

=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

 

[Ragdyer]

This will always refer to Z183:

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