I have the following formula as an example on a sheet called Summary....

=IF(ISBLANK(LocationOne!L18),"",LocationOne!L18)

The formula is copied down a range of rows from L6 to L75.

When I delete row 18 on the LocationOne sheet, it creates a row where the
#REF! is displayed. the formula in the cell above indicates L18, and the
formula in the cell below indicates L19....What is causing this extra row,
and how can I prevent it?

Thanks

Deleting cells that are referenced in a formula causes that reference to
become invalid. When the cells are shifted up the other formulas self adjust.
The only thing you can do is to delete the formula (or the entire row) where
the reference is no longer valid. Generally speaking you do not want to
delete cells that are refenced in other formulas as you end up having to
remove all of the invalid references that it leaves behind.
--
HTH...

Jim Thomlinson


"carrera" wrote:

I have the following formula as an example on a sheet called Summary....

=IF(ISBLANK(LocationOne!L18),"",LocationOne!L18)

The formula is copied down a range of rows from L6 to L75.

When I delete row 18 on the LocationOne sheet, it creates a row where the
#REF! is displayed. the formula in the cell above indicates L18, and the
formula in the cell below indicates L19....What is causing this extra row,
and how can I prevent it?

Thanks

Well poo.

How about a macro that would automatically delete whatever row is now invalid?

How would I state that no matter which row is now #REF!, to delete it?

Thanks Jim, you're very helpful.



"Jim Thomlinson" wrote:

Deleting cells that are referenced in a formula causes that reference to
become invalid. When the cells are shifted up the other formulas self adjust.
The only thing you can do is to delete the formula (or the entire row) where
the reference is no longer valid. Generally speaking you do not want to
delete cells that are refenced in other formulas as you end up having to
remove all of the invalid references that it leaves behind.
--
HTH...

Jim Thomlinson


"carrera" wrote:

I have the following formula as an example on a sheet called Summary....

=IF(ISBLANK(LocationOne!L18),"",LocationOne!L18)

The formula is copied down a range of rows from L6 to L75.

When I delete row 18 on the LocationOne sheet, it creates a row where the
#REF! is displayed. the formula in the cell above indicates L18, and the
formula in the cell below indicates L19....What is causing this extra row,
and how can I prevent it?

Thanks