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.

Hi

I'm not sure exactly what you are trying to achieve but if you have a
Text date of say January 2006 in say cell A1
=A1+0
will turn that into a serial date
If you then format that cell FormatCellsNumberCustom dd/mmm/yy you
will get 01/Jan/2006

Not sure whether this helps you any.

--
Regards

Roger Govier


"mr tom" <mr-tom at mr-tom.co.uk.(donotspam) wrote in message
...
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.

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.

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.

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.

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.

Thanks for answering, Roger.

What I need to do is chart (individually) the 12 months prior to the
reporting one, so in essence, I need to find the current month (may not be
TODAY()) and return results from one row above, two rows above etc.

I've actually managed to manipulate the Biz Intelligence app to do this, but
I'd still love to work out how Excel would do it.

Essentially, rather than VLookup or HLookup, it's a diagonal lookup on
different vectors.

Am I making any sense?

Cheers,

Tom.

"Roger Govier" wrote:

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.

Hi Tom

It makes no difference whether the data is horizontal or vertical, or
whether you want to find the current month and count backward, or find
the earlier month and count forward.

Try
=SUM(INDEX($C$3:C100,MATCH($A$1,B$3:B$100,0)):
INDEX($C$3:$C100,MATCH($A$1,$B$3:$B$100,0)-11))

(I made a mistake earlier, it should have been 11 months offset, not
12).

If you are wanting to produce a chart, then create a defined name.
InsertNameDefine MyData
Refers to
=INDEX($B$3:$N$30,MATCH($A$1,$B$3:$B$30,0)-11,1):
INDEX($B$3:$N$30,MATCH($A$1,$B$3:$B$30,0),12)

Give the Chart =Mydata as its source.

The above assumes that Months are in column B and data extends through
columns C to N
Entering March )7 in cell A1 would define the data as being from Apr 06
to Mar O7 and produce a range of
B6:N16
Change references to suit.

--
Regards

Roger Govier


"mr tom" <mr-tom at mr-tom.co.uk.(donotspam) wrote in message
...
Thanks for answering, Roger.

What I need to do is chart (individually) the 12 months prior to the
reporting one, so in essence, I need to find the current month (may
not be
TODAY()) and return results from one row above, two rows above etc.

I've actually managed to manipulate the Biz Intelligence app to do
this, but
I'd still love to work out how Excel would do it.

Essentially, rather than VLookup or HLookup, it's a diagonal lookup on
different vectors.

Am I making any sense?

Cheers,

Tom.

"Roger Govier" wrote:

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.