Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to update/change cell references in a formula
I have a formula in which the cell references need to be updated/changed each
month, when an additional row and column of data are added to the dataset. For example, the formula is: Factor = SUM(C2,D3,E4)/SUM(B2,C3,D4) and the data is arranged as follows: a b c d e 1 16 79 17 13 14 2 - 58 49 88 14 3 - - 77 76 14 4 - - - 93 67 5 - - - - 23 The following month, an additional row and column of data is added... a b c d e f 1 16 79 17 13 14 17 2 - 58 49 88 14 10 3 - - 77 76 14 11 4 - - - 93 67 97 5 - - - - 23 83 6 - - - - - 41 at which time the formula needs to be updated as follows: Factor = SUM(D3,E4,F5)/SUM(C3,D4,E5) Any suggestions on how I might go about doing this, perhaps with a macro? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to update/change cell references in a formula
It is not clear in which cell you have this formula...
Assuming you have the following in H8 = SUM(C2,D3,E4)/SUM(B2,C3,D4) Then if you copy this formula and paste to I9, it will automatically change to = SUM(D3,E4,F5)/SUM(C3,D4,E5) Keep copying it diagnally to let it adjust itself... Am I missing something here? "510 Financeguy" wrote: I have a formula in which the cell references need to be updated/changed each month, when an additional row and column of data are added to the dataset. For example, the formula is: Factor = SUM(C2,D3,E4)/SUM(B2,C3,D4) and the data is arranged as follows: a b c d e 1 16 79 17 13 14 2 - 58 49 88 14 3 - - 77 76 14 4 - - - 93 67 5 - - - - 23 The following month, an additional row and column of data is added... a b c d e f 1 16 79 17 13 14 17 2 - 58 49 88 14 10 3 - - 77 76 14 11 4 - - - 93 67 97 5 - - - - 23 83 6 - - - - - 41 at which time the formula needs to be updated as follows: Factor = SUM(D3,E4,F5)/SUM(C3,D4,E5) Any suggestions on how I might go about doing this, perhaps with a macro? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to update/change cell references in a formula
Unfortunately, I cannot copy the formula down the diagonal to a different
cell...the location of the formula must remain static. "Sheeloo" wrote: It is not clear in which cell you have this formula... Assuming you have the following in H8 = SUM(C2,D3,E4)/SUM(B2,C3,D4) Then if you copy this formula and paste to I9, it will automatically change to = SUM(D3,E4,F5)/SUM(C3,D4,E5) Keep copying it diagnally to let it adjust itself... Am I missing something here? "510 Financeguy" wrote: I have a formula in which the cell references need to be updated/changed each month, when an additional row and column of data are added to the dataset. For example, the formula is: Factor = SUM(C2,D3,E4)/SUM(B2,C3,D4) and the data is arranged as follows: a b c d e 1 16 79 17 13 14 2 - 58 49 88 14 3 - - 77 76 14 4 - - - 93 67 5 - - - - 23 The following month, an additional row and column of data is added... a b c d e f 1 16 79 17 13 14 17 2 - 58 49 88 14 10 3 - - 77 76 14 11 4 - - - 93 67 97 5 - - - - 23 83 6 - - - - - 41 at which time the formula needs to be updated as follows: Factor = SUM(D3,E4,F5)/SUM(C3,D4,E5) Any suggestions on how I might go about doing this, perhaps with a macro? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do update formula when change a number in another cell | New Users to Excel | |||
copy formula down a column and have cell references change within formula | New Users to Excel | |||
Cell references auto update when sorting | Excel Discussion (Misc queries) | |||
Cell references do not update | Excel Discussion (Misc queries) | |||
Adding a row to worksheet does not update cell references in another. | Excel Worksheet Functions |