Should be easy, but...
Hi Tom
If your Months January 2006, February 2006 etc are in cells B1:Z1 with
your numeric data in rows 2 onward, then you could use the formula
=SUM(INDEX(B2:Z2,MATCH($A$1,B$1:Z$1,0)):INDEX(B2:Z 2,12))
It would find the "month year" entered by you in A1, and starting from
that column would sum 12 columns of data.
--
Regards
Roger Govier
"mr tom" <mr-tom at mr-tom.co.uk.(donotspam) wrote in message
...
Actually - just read that a second time. The formula will do the job
as it
allows me to use one month to generate others as text values to
include in a
vlookup (although I suspect it'll fall over when it tries to move back
from
Jan 07 to Dec 06).
Cheers,
Tom.
"mr tom" wrote:
In essence, there will be a feed from a data warehouse dumping say 24
months
of data into a sheet.
I want to select and chart data from the 12 months prior to the
current one.
Forget =TODAY() etc as I could run the report at any time. I'll just
have
the month and year entered into a cell (there's a master control for
the
reporting, so I'll use that.
What I'm trying to do is, e.g. if the reporting month is Feb 07, I
need to
see the year to (but excluding feb), i.e. I need to pick all the
entries from
Feb 06 to Jan 07.
Is this any clearer?
Cheers,
"vezerid" wrote:
Lemme see if I understand. You have the "dates" (months actually as
I
see them) in something like A2:A13. And you have, say, February
2007
in another cell, say F2. What would you like to get? January 2007?
If
so you do not really need the table in A2:A13.
=TEXT(DATE(YEAR(DATEVALUE(1&" "&F2)),MONTH(DATEVALUE(1&"
"&F2))-1,1),"Mmmm yyyy")
Does this help?
Kostis Vezerides
On Mar 6, 3:06 pm, mr tom <mr-tom at mr-tom.co.uk.(donotspam)
wrote:
OK.
I've got a list of dates expressed as text:
January 2006
February 2006
...
...
...
December 2007
I've then got a cell containing the curret reporting period,
expressed as
text, February 2007
I'm looking for a formula which will look up the current
reporting period
within my list of dates and return the text date above it.
Ultimately, I'll then use a VLookup to return the adjacent data,
but I can
manage that without too much help.
I know this should be easy, but I've been messing about with this
for an
hour or so now, so...
Any help appreciated.
Tom.
|