Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Maintain Formula Reference (sort of)
Hi,
If I have a simple speadsheet: A B C D 1 1 2 3 (formula is =A1+B1) 2 3 4 7 (formula is =A2+B2) 3 5 6 11 (formula is =A3+B3) Column D simply adds up the number from A1 and B1, etc. However, what I want to be able to do is, for example, cut cells A3&B3 and "Insert Cut Cells" over, say A1&B1, so that the values in row 3 are moved to row 1 and the rest of rows each shift down one. However, when I do this, the formulas in column D maintain their original reference. In other words, the spreadsheet becomes: A B C D 1 5 6 3 (formula is =A2+B2) 2 1 2 7 (formula is =A3+B3) 3 3 4 11 (formula is =A1+B1) But this is wrong because I need colum D to ALWAYS be the sum of the A and B values from the same row. Is there a way to format these formulas so that regardless of where I "move" the data, the formula always sums the vaules in the same row? Thansk in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Maintain Formula Reference (sort of)
Hi Jim
One way =INDEX(A:A,ROW())+INDEX(B:B,ROW()) copy down as far as required -- Regards Roger Govier "JimK" wrote in message ... Hi, If I have a simple speadsheet: A B C D 1 1 2 3 (formula is =A1+B1) 2 3 4 7 (formula is =A2+B2) 3 5 6 11 (formula is =A3+B3) Column D simply adds up the number from A1 and B1, etc. However, what I want to be able to do is, for example, cut cells A3&B3 and "Insert Cut Cells" over, say A1&B1, so that the values in row 3 are moved to row 1 and the rest of rows each shift down one. However, when I do this, the formulas in column D maintain their original reference. In other words, the spreadsheet becomes: A B C D 1 5 6 3 (formula is =A2+B2) 2 1 2 7 (formula is =A3+B3) 3 3 4 11 (formula is =A1+B1) But this is wrong because I need colum D to ALWAYS be the sum of the A and B values from the same row. Is there a way to format these formulas so that regardless of where I "move" the data, the formula always sums the vaules in the same row? Thansk in advance. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Maintain Formula Reference (sort of)
"JimK" wrote in message
... Hi, If I have a simple speadsheet: A B C D 1 1 2 3 (formula is =A1+B1) 2 3 4 7 (formula is =A2+B2) 3 5 6 11 (formula is =A3+B3) Column D simply adds up the number from A1 and B1, etc. However, what I want to be able to do is, for example, cut cells A3&B3 and "Insert Cut Cells" over, say A1&B1, so that the values in row 3 are moved to row 1 and the rest of rows each shift down one. However, when I do this, the formulas in column D maintain their original reference. In other words, the spreadsheet becomes: A B C D 1 5 6 3 (formula is =A2+B2) 2 1 2 7 (formula is =A3+B3) 3 3 4 11 (formula is =A1+B1) But this is wrong because I need colum D to ALWAYS be the sum of the A and B values from the same row. Is there a way to format these formulas so that regardless of where I "move" the data, the formula always sums the vaules in the same row? Thansk in advance. In D1, try =OFFSET(D1,0,-3)+OFFSET(D1,0,-2) -- David Biddulph |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
circular reference formula | Excel Discussion (Misc queries) | |||
How can I sort by the value of my formula and not actual formula | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
How do I make a formula refer to given data even if I do a sort f. | Excel Discussion (Misc queries) |