Change Formula Reference Each Month

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

Re: Range To Increase Based On Critiera

jwilso6, [fa]*[/fa]

Re: Change Formula Reference Each Month

Quote

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

Please clarify:

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?

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.

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.

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.