Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows without losing formulas
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows without losing formulas
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows without losing formulas
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows without losing formulas
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" wrote in message ... 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows without losing formulas
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" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protect Formulas while deleting rows | Excel Discussion (Misc queries) | |||
Way to keep formulas unchanged when deleting rows? | Excel Discussion (Misc queries) | |||
deleting rows messing up formulas | Excel Discussion (Misc queries) | |||
Adding and deleting rows with formulas ****Need Help**** | Excel Discussion (Misc queries) | |||
deleting rows--calculating formulas with each row as it goes? | Excel Discussion (Misc queries) |