Deleting rows without losing formulas

[John Davies]

If I have a column of cells, say A1 to A15 with the values of A1=1,
A2=A1+1,A3=A2+1 etc, then if I want to delete say row A6, row A7 will show
the #REF! error. Is there a way to delete the row without losing the
formula in the next row?

[Lionel H]

John,
Instead of a2=A1+1, A3=A2+1 etc
try a2=offset(a2,-1,0)+1, a3=offset(a3,-1,0)+1 etc
regards,
Lionel


"John Davies" wrote:

> If I have a column of cells, say A1 to A15 with the values of A1=1,
> A2=A1+1,A3=A2+1 etc, then if I want to delete say row A6, row A7 will show
> the #REF! error. Is there a way to delete the row without losing the
> formula in the next row?

[John Davies]

Hi Lionel

That works fine, but if I want to insert a line, the formula recalculates
from the inserted point. Is there a way to delete and insert lines without
losing the format.

Also to be a pain, say that a column of cells has formulae that refer to a
lookup table e.g. a2=vlookup(e12,data,5), a3=vlookup(e13,data,5) etc is there
a way to delete and insert rows without losing the formulas in the next row.

Thanks for your help


"John Davies" wrote:

> If I have a column of cells, say A1 to A15 with the values of A1=1,
> A2=A1+1,A3=A2+1 etc, then if I want to delete say row A6, row A7 will show
> the #REF! error. Is there a way to delete the row without losing the
> formula in the next row?

[David McRitchie]

Hi John,
See Insert a Row using a Macro to maintain formulas
http://www.mvps.org/dmcritchie/excel/insrtrow.htm

A VERY important aspect of this is changing your formula to use
OFFSET so that you can insert, delete, sort rows.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"John Davies" <JohnDavies@discussions.microsoft.com> wrote in message news:D79A5974-F714-4761-B28D-B4BB3669A4AF@microsoft.com...
> Hi Lionel
>
> That works fine, but if I want to insert a line, the formula recalculates
> from the inserted point. Is there a way to delete and insert lines without
> losing the format.
>
> Also to be a pain, say that a column of cells has formulae that refer to a
> lookup table e.g. a2=vlookup(e12,data,5), a3=vlookup(e13,data,5) etc is there
> a way to delete and insert rows without losing the formulas in the next row.
>
> Thanks for your help
>
>
> "John Davies" wrote:
>
> > If I have a column of cells, say A1 to A15 with the values of A1=1,
> > A2=A1+1,A3=A2+1 etc, then if I want to delete say row A6, row A7 will show
> > the #REF! error. Is there a way to delete the row without losing the
> > formula in the next row?

[Dana DeLouis]

Another option, with some warnings, might be a named formula.

ActiveWorkbook.Names.Add "Add1", "=R[-1]C+1"

Now, in A2, enter the formula : =Add1
and copy down.
Just don't copy the sheet, as this will cause errors.

--
Dana DeLouis
Win XP & Office 2003


"John Davies" <JohnDavies@discussions.microsoft.com> wrote in message
news:B0A5BC5D-B28F-4AAA-97FD-6345F901A5EB@microsoft.com...
> If I have a column of cells, say A1 to A15 with the values of A1=1,
> A2=A1+1,A3=A2+1 etc, then if I want to delete say row A6, row A7 will show
> the #REF! error. Is there a way to delete the row without losing the
> formula in the next row?