Hi,

I need a formula to return the value of a cell from a nearby column, based on
a matching of the months and years within a range of dates to the months and
years within a given date. Here is the setup:

Cell BA58 contains the given date.

Column AP, starting in Cell AP59 and going down to AP2000 contains the array
of dates that need to be evaluated to find which date matches the given date
in Cell BA58

Column AR, starting in Cell AR59 and going down to AR2000 contains the array
of numbers from which the result must be displayed. If a match is found
between the given date in Cell BA58 and a date within the array of dates in
AP59:2000, then the result should be the cell value taken from the same row
from column of numbers AR59:2000.

The dates from AP59 and down look like this:
12 Dec 2009
14 Jan 2010
12 Feb 2010
12 Mar 2010
12 Apr 2010
12 May 2010
Etc.

The given date in BA58 looks like this: 03/2009

The numbers from AR59 and down look like this:
0
9
8
7
4
3
Etc.


This is probably easy when one knows how, but I don't -- so I am stuck.

Help appreciated.

GBExcel

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/201001/1

Will the date in BA58 appear *only once* (or possibly, not at all) in
AP59:AP2000?

--
Biff
Microsoft Excel MVP


"GBExcel via OfficeKB.com" <u55438@uwe wrote in message
news:a273c2bcb0391@uwe...
Hi,

I need a formula to return the value of a cell from a nearby column, based
on
a matching of the months and years within a range of dates to the months
and
years within a given date. Here is the setup:

Cell BA58 contains the given date.

Column AP, starting in Cell AP59 and going down to AP2000 contains the
array
of dates that need to be evaluated to find which date matches the given
date
in Cell BA58

Column AR, starting in Cell AR59 and going down to AR2000 contains the
array
of numbers from which the result must be displayed. If a match is found
between the given date in Cell BA58 and a date within the array of dates
in
AP59:2000, then the result should be the cell value taken from the same
row
from column of numbers AR59:2000.

The dates from AP59 and down look like this:
12 Dec 2009
14 Jan 2010
12 Feb 2010
12 Mar 2010
12 Apr 2010
12 May 2010
Etc.

The given date in BA58 looks like this: 03/2009

The numbers from AR59 and down look like this:
0
9
8
7
4
3
Etc.


This is probably easy when one knows how, but I don't -- so I am stuck.

Help appreciated.

GBExcel

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/201001/1

I'm not sure that I understand your question, but I hope that this helps.

The date in BA58 is derived as follows:

=TEXT(TODAY(),"mm/yyyy")

It could also be any date such as
=TEXT([Any date],"mm/yyyy")

I am using BA58 as a control so that I can call data and produce reports
based on the date in BA58.

Thanking you.
GBExcel


T. Valko wrote:
Will the date in BA58 appear *only once* (or possibly, not at all) in
AP59:AP2000?

Hi,

[quoted text clipped - 46 lines]

GBExcel

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/201001/1

Here's what you posted:

----------
Cell BA58 contains the given date.

Column AP, starting in Cell AP59 and going down to AP2000 contains the array
of dates that need to be evaluated to find which date matches the given date
in Cell BA58
----------

My question to you is:

Will the date in cell BA58 appear *only once* in the range AP59:AP2000?

The date in BA58 is derived as follows:
=TEXT(TODAY(),"mm/yyyy")

Let's assume BA58 = 012010

In the range AP59:AP2000, will there be *only one instance* of a date in
January 2010? Based on your posted sample data:

----------
12 Dec 2009
14 Jan 2010
12 Feb 2010
12 Mar 2010
12 Apr 2010
12 May 2010
Etc.
----------

There is *only one* date that meets the condition. However, the amount of
sample data posted might not be a true representative sample of your real
data.

--
Biff
Microsoft Excel MVP


"GBExcel via OfficeKB.com" <u55438@uwe wrote in message
news:a2741157ebca5@uwe...
I'm not sure that I understand your question, but I hope that this helps.

The date in BA58 is derived as follows:

=TEXT(TODAY(),"mm/yyyy")

It could also be any date such as
=TEXT([Any date],"mm/yyyy")

I am using BA58 as a control so that I can call data and produce reports
based on the date in BA58.

Thanking you.
GBExcel


T. Valko wrote:
Will the date in BA58 appear *only once* (or possibly, not at all) in
AP59:AP2000?

Hi,

[quoted text clipped - 46 lines]

GBExcel

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/201001/1

Will the date in cell BA58 appear *only once* in the range AP59:AP2000?

Yes, that is correct.

GBExcel

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/201001/1

Try this...

=SUMPRODUCT(--(TEXT(AP59:AP2000,"mmyyyy")=BA58),AR59:AR2000)

--
Biff
Microsoft Excel MVP


"GBExcel via OfficeKB.com" <u55438@uwe wrote in message
news:a274c36192884@uwe...
Will the date in cell BA58 appear *only once* in the range AP59:AP2000?

Yes, that is correct.

GBExcel

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/201001/1