You're welcome, and thank you for the feed-back.
In fact, because of your response, I see that I posted one of my test
formulas.by mistake.
You can try this revised, abbreviated version:
=SUM(INDEX(B2:B50,MATCH(TODAY(),A2:A50)+0):INDEX(B2:B50,MATCH(TODAY(),A2:A50)+11))
When I eliminated the "match type" argument of "0" (exact), that made
Today() an adequate lookup value on it's own, but I didn't post that version
after I finished testing.
Sorry!
As for what the formula does, you first of all see that it's a Sum()
formula.
Picture it totaling 12 rows, something like this:
=Sum(B16:B27)
Since the rows are in chronological order, and you needed 12 months totaled
up, it should be easy to comprehend that all we have to do to solve this, is
find the starting point of the sum range and then just simply extend it to
include 12 rows.
The Index() function is sort of complicated.
It can return a value that's in a cell, OR, the reference to the cell.
In this case we're using Index() in it's "reference" form, where it will
return a cell reference, *not* the contents of a cell.
As an example of these different forms, we index the Column B range that
contains the forecasts:
B2:B50
And let's start with B16, which is the *15th CELL* in that range:
=Index(B2:B50,15)
If B16 contains 100, the above formula will return that value ... 100.
If, say, H2 contained the number 15, then
=Index(B2:B50,H2)
Would do the same.
On the other hand, if we use it this way:
=Sum(Index(B2:B50,15):B27)
It will give us *exactly* the same results as:
=Sum(B16:B27)
Which will give us *exactly* the same results as:
=Sum(Index(B2:B50,15):Index(B2:B50,15+11))
So you see, all we really need to do is find out what actual number to place
in the position of the "15" that we used in the example.
This is accomplished using the Match() function.
Picture Match() as being a Vlookup or Hlookup function, *BUT* ... it returns
a reference to a cell, *NOT* the value in the cell!
And that reference is a *relative* reference, relating to the *position* of
the cell within the match range.
The syntax is:
Match(lookup value, lookup array, type of match)
Where "match type" is optional, and can be:
0 = Exact Match - lookup array can be in any order
omitted or 1 = Largest value that is less then or equal to lookup value -
lookup array must be sorted ascending
-1 = Smallest value that is greater then or equal to lookup value - lookup
array must be sorted descending
From here on, it's pretty much straight forward.
If you had, within the range - A2 to A50:
A22 = Sept-07
A23 = Oct-07
A24 = Nov-07
=Match(Today(),A2:A50)
Would return the number "22",
Which is A23 - Oct-07 - the *22nd CELL* in the lookup range.
So, if we now go back to our Sum formula, using the Index function:
=Sum(Index(B2:B50,15):Index(B2:B50,15+11))
Where we said that all we had to do was determine the proper value to use in
place of "15" to find the starting point,
We replace 15 with the Match formula:
=Sum(Index(B2:B50,Match(Today(),A2:A50)):Index(B2:B50,15+11))
And we also replace the second "15", and add the "+11":
=Sum(Index(B2:B50,Match(Today(),A2:A50)):Index(B2:B50,Match(Today(),A2:A50)+11))
And there you have your completed formula.
