Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Insert a new line, without destroying the consistency of the formu
How do I insert a new line in a excel spreadsheet without destroying the
consistency of the formulas? Cut down to a minimum, the following is what I want. Starting point: B2=B1+A2 B3=B2+A3 Desired result after inserting a new line BETWEEN line 2 and line 3: B2=B1+A2 B3=B2+A3 B4=B3+A4 I have been using SuperCalc for many years now, where this is a very simple command. But I have newer been able to make the shift to excel. Just because I could not figure out how to make this simple insert. I have been trying at least four different types of relative referencing, but they are all producing the wrong result: B2=B1+A2 B3=<empty B4=B2+A4 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Insert a new line, without destroying the consistency of the formu
=INDIRECT("B"&ROW()-1)+INDIRECT("A"&ROW())
"HPH" wrote: How do I insert a new line in a excel spreadsheet without destroying the consistency of the formulas? Cut down to a minimum, the following is what I want. Starting point: B2=B1+A2 B3=B2+A3 Desired result after inserting a new line BETWEEN line 2 and line 3: B2=B1+A2 B3=B2+A3 B4=B3+A4 I have been using SuperCalc for many years now, where this is a very simple command. But I have newer been able to make the shift to excel. Just because I could not figure out how to make this simple insert. I have been trying at least four different types of relative referencing, but they are all producing the wrong result: B2=B1+A2 B3=<empty B4=B2+A4 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Insert a new line, without destroying the consistency of theformu
i think what the original poster is looking for is that the formula
will automatically populate the newly-inserted line, in the same column as the previous row (& the row after)............. if i understand him/her properly. the only way i can see to do it is to insert the new line, go to the first cell above that contains the formula, and pull it down throughout the rest of the list, thereby inserting the formula in the blank cell & also correcting the rest of the list. :) susan On May 19, 10:26*am, Teethless mama wrote: =INDIRECT("B"&ROW()-1)+INDIRECT("A"&ROW()) "HPH" wrote: How do I insert a new line in a excel spreadsheet without destroying the consistency of the formulas? Cut down to a minimum, the following is what I want. Starting point: *B2=B1+A2 *B3=B2+A3 Desired result after inserting a new line BETWEEN line 2 and line 3: *B2=B1+A2 *B3=B2+A3 *B4=B3+A4 I have been using SuperCalc for many years now, where this is a very simple command. But I have newer been able to make the shift to excel. Just because I could not figure out how to make this simple insert. I have been trying at least four different types of relative referencing, but they are all producing the wrong result: *B2=B1+A2 *B3=<empty *B4=B2+A4- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Insert a new line, without destroying the consistency of the f
Hello Susan
You are absolutely right. But this is just the simplest example. In my real spreadsheet there are hundreds of columns and rows, and many inserts. So it is very tedious and risky to do this mouse maneuver. I would be very thankful for a real solution. "Susan" wrote: i think what the original poster is looking for is that the formula will automatically populate the newly-inserted line, in the same column as the previous row (& the row after)............. if i understand him/her properly. the only way i can see to do it is to insert the new line, go to the first cell above that contains the formula, and pull it down throughout the rest of the list, thereby inserting the formula in the blank cell & also correcting the rest of the list. :) susan On May 19, 10:26 am, Teethless mama wrote: =INDIRECT("B"&ROW()-1)+INDIRECT("A"&ROW()) "HPH" wrote: How do I insert a new line in a excel spreadsheet without destroying the consistency of the formulas? Cut down to a minimum, the following is what I want. Starting point: B2=B1+A2 B3=B2+A3 Desired result after inserting a new line BETWEEN line 2 and line 3: B2=B1+A2 B3=B2+A3 B4=B3+A4 I have been using SuperCalc for many years now, where this is a very simple command. But I have newer been able to make the shift to excel. Just because I could not figure out how to make this simple insert. I have been trying at least four different types of relative referencing, but they are all producing the wrong result: B2=B1+A2 B3=<empty B4=B2+A4- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Insert a new line, without destroying the consistency of the f
Hello Teethless
I have just tested your solution. And it actually has the right behavior when inserting a new line. Thank you very much. Now I just have to figure out how to insert a line with the right formulas with one key combination. But this must be a simple operation described somewhere in the documentation. "Teethless mama" wrote: =INDIRECT("B"&ROW()-1)+INDIRECT("A"&ROW()) "HPH" wrote: How do I insert a new line in a excel spreadsheet without destroying the consistency of the formulas? Cut down to a minimum, the following is what I want. Starting point: B2=B1+A2 B3=B2+A3 Desired result after inserting a new line BETWEEN line 2 and line 3: B2=B1+A2 B3=B2+A3 B4=B3+A4 I have been using SuperCalc for many years now, where this is a very simple command. But I have newer been able to make the shift to excel. Just because I could not figure out how to make this simple insert. I have been trying at least four different types of relative referencing, but they are all producing the wrong result: B2=B1+A2 B3=<empty B4=B2+A4 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Insert a new line, without destroying the consistency of the f
Everything was fine, until i inserted a column...
Your clever trick unfortunately can't cope with column inserts. So I am open to new suggestions. "Teethless mama" wrote: =INDIRECT("B"&ROW()-1)+INDIRECT("A"&ROW()) "HPH" wrote: How do I insert a new line in a excel spreadsheet without destroying the consistency of the formulas? Cut down to a minimum, the following is what I want. Starting point: B2=B1+A2 B3=B2+A3 Desired result after inserting a new line BETWEEN line 2 and line 3: B2=B1+A2 B3=B2+A3 B4=B3+A4 I have been using SuperCalc for many years now, where this is a very simple command. But I have newer been able to make the shift to excel. Just because I could not figure out how to make this simple insert. I have been trying at least four different types of relative referencing, but they are all producing the wrong result: B2=B1+A2 B3=<empty B4=B2+A4 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Insert a new line, without destroying the consistency of the f
well actually it won't be a problem in my current calculations. It was just
an observation during testing. I had another advice to create the following Visual Basic code: Function RelativeCellValue(Optional column, Optional row) Application.Volatile answer = Application.Caller.Offset(row, column) RelativeCellValue = answer End Function It provides a complete relative referencing. "HPH" wrote: Everything was fine, until i inserted a column... Your clever trick unfortunately can't cope with column inserts. So I am open to new suggestions. "Teethless mama" wrote: =INDIRECT("B"&ROW()-1)+INDIRECT("A"&ROW()) "HPH" wrote: How do I insert a new line in a excel spreadsheet without destroying the consistency of the formulas? Cut down to a minimum, the following is what I want. Starting point: B2=B1+A2 B3=B2+A3 Desired result after inserting a new line BETWEEN line 2 and line 3: B2=B1+A2 B3=B2+A3 B4=B3+A4 I have been using SuperCalc for many years now, where this is a very simple command. But I have newer been able to make the shift to excel. Just because I could not figure out how to make this simple insert. I have been trying at least four different types of relative referencing, but they are all producing the wrong result: B2=B1+A2 B3=<empty B4=B2+A4 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complex sheets - how can I delete rows without destroying formulas? | Links and Linking in Excel | |||
Insert New Line | Excel Discussion (Misc queries) | |||
Macro Line Insert | Excel Discussion (Misc queries) | |||
Improve consistency in how Excel and Word 2003 close. | Excel Discussion (Misc queries) | |||
Insert each line in a new row | Excel Discussion (Misc queries) |