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?

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?

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?