 # Change Formula Reference Each Month

• Hello everyone.

I have the below formula that I need help on improving and I'm at an impass. Each month the below formula is moved down one row. How could I improve the below formula. I've attached a spreadsheet to further explain my problems.

Formula in cell f107 Month one would be
=+SUM(F43:F43,F49:F49,F55:F55,F61:F61,F67:F67)

Formula in cell f107 Month two would be
=+SUM(I43:I44,I49:I50,I55:I56,I61:I62,I67:I68)

Formula in cell f107 Month three would be
=+SUM(L39:L39,L43:L45,L49:L51,L55:L57,L61:L63,L67:L69)

Thanks for anyone's help

## Files

• Re: Range To Increase Based On Critiera

Not sure how to improve the formula itself (aside from removing the unnecessary + at the beginning), but you could just highlight it, Ctrl+H, F in the first box, I in the second, and click Replace. You would just do this every month.

• Re: Range To Increase Based On Critiera

Why to use F43:F43 instead of F43 etc..
Why in the formulae has been select these rows: 43, 49, 55, 61, 67?
If we could stick the rows we could make a SUMIF

Triumph without peril brings no glory: Just try

• Re: Change Formula Reference Each Month

Quote

Each month the below formula is moved down one row. How could I improve the below formula.

Does the formula move down one row because of the insertion of a row somewhere above row 107 such that there is always one row with a sum formula although it be altered each month.

-- or –

Is the formula copied down one row such that there will be twelve rows of formulas by the end of the year.

BTW I noticed in your sample that cell F43 is on the row for Feb. Did you mean it to be on the row for Jan?

[FONT="Arial Black"][COLOR="blue"][SIZE="4"]Bill[/SIZE][/COLOR][/FONT]
Tip: To avoid chasing code always use Option Explicit.

• Re: Change Formula Reference Each Month

Bill,

Thanks for the reply. Just to clarify, you are correct, the formula is copied down one row each month so there will be twelve rows at the end of the year or 12 month period.

Joe

• Re: Change Formula Reference Each Month

Enter this formula in F107 and copy down

= SUM(F\$43:F43,F\$49:F49,F\$55:F55,F\$61:F61,F\$67:F67)

However, I notice that at month 7 your summation groups begin to overlap because your groups start every 7 rows not every 12 rows. And your formula starts in Feb, not Jan

To sum groups of 12 months starting Jan of your sample workbook:

= SUM(G\$42:G42,G\$54:G54,G\$66:G66,G\$78:G78,G\$90:G90)

I don’t understand your sample formulas in the body of your post.

Month 1 is column F
Month 2 is column I
Month 3 is column L

If your copying a formula down should each successive formula be in the same column?

Did you mean copy the formula accross every third column and at the same time increment the number of rows summed?

I clear explanation would save us all a lot of time.

[FONT="Arial Black"][COLOR="blue"][SIZE="4"]Bill[/SIZE][/COLOR][/FONT]
Tip: To avoid chasing code always use Option Explicit.

• Re: Change Formula Reference Each Month

Bill,

Thanks for your help. I really appreciate. I too do not understand this formula quite well as it was given to me to fix. After exhausting all my resources, I've decided to tell them it's not worth it and they need to figure out a better method.

You've made some very good points and I appreciated all your help.

Sorry about the confusion, and since there wasn't a clear explanation, you can delete this thread.

Joe

• Re: Change Formula Reference Each Month

You giveup so easily. the formula is sound. What you need to do is find out what needs to be totaled and were the totals go. Then determine the formula you need. I'll still be glad to help if you can give me that information.

[FONT="Arial Black"][COLOR="blue"][SIZE="4"]Bill[/SIZE][/COLOR][/FONT]
Tip: To avoid chasing code always use Option Explicit.

• Re: Change Formula Reference Each Month

Don't give up!

From your problem i have deduced that you want the value of a column in a given time period and then the 6 month value, 12 month value, 18, 24, and 30 month values (in 6 month increments).

Unfortunately the months are fixed but the days are sporadic so EDATE is out of the question.

I have posted a file that has a solution for you via index & Match for the date criteria and DSUM for the totals.

EXAMPLE:

DSUM CRITERIA
MONTH
Initial Date Criteria
6 Month Date "Formula Criteria"
12 Month Date "Formula Criteria"
18 Month Date "Formula Criteria"
24 Month Date "Formula Criteria"
30 Month Date "Formula Criteria"

=Initial Date
6 mo Date = INDEX(DataRange,MATCH(Initial Date REFERENCED above,DataRange,0)+6,1)
12 mo Date = INDEX(DataRange,MATCH(6 mo Date REFERENCED above,DataRange,0)+6,1)
and so on.... for the 12, 18, 24, and 30 respectively

Below that is the DSUM formula.

Dsum(Database,ColumnNumber,Month:30 Month Date)

Explanation:
Essentially the index match offsets a specified cell 6 rows down and then copied down offsets the preceding cell six rows down so you have a chain of cells offset 6 rows each

This provides the criteria necessary for the D sum. ASSUMING fixed month intervals but sporadic days.

I have also posted a sample with a scroll bar to make the process more dynamic

Regards,

Dude 8-)

P.S. The download takes a sec but you will eventually be able to click "OK" and look at the spreadsheet.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!