Help needed on date matching and cell reference.

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.aspx/excel-functions/201001/1

0
GBExcel
1/21/2010 5:48:36 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

9 Replies
650 Views

Similar Articles

[PageSpeed] 50

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.aspx/excel-functions/201001/1
> 


0
T
1/21/2010 6:03:01 PM
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.aspx/excel-functions/201001/1

0
GBExcel
1/21/2010 6:23:48 PM
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.aspx/excel-functions/201001/1
> 


0
T
1/21/2010 7:05:43 PM
>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.aspx/excel-functions/201001/1

0
GBExcel
1/21/2010 7:43:31 PM
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.aspx/excel-functions/201001/1
> 


0
T
1/21/2010 8:03:52 PM
Thank you. Will try it.

The double minus (as in "--") is new to me. What does it do?

GBExcel

T. Valko wrote:
>Try this...
>
>=SUMPRODUCT(--(TEXT(AP59:AP2000,"mmyyyy")=BA58),AR59:AR2000)
>
>> >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.aspx/excel-functions/201001/1

0
GBExcel
1/21/2010 8:13:02 PM
>=SUMPRODUCT(--(TEXT(AP59:AP2000,"mmyyyy")=BA58),AR59:AR2000)
>The double minus (as in "--") is new to me.
>What does it do?

The SUMPRODUCT function works with numbers. It multiplies arrays of numbers 
together then returns the sum total of that multiplication. For example:

1...3
2...4
6...2

=SUMPRODUCT(A1:A3,B1:B3) = 23

(1*3)+(2*4)+(6*2) = 23

In this formula:

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

We have one array of numbers in the range AR59:AR2000. So, we need to come 
up with another array of numbers in order for the SUMPRODUCT function to do 
what it does.

This expression will return an array of either TRUE or FALSE:

TEXT(AP59:AP2000,"mmyyyy")=BA58

Based on your posted sample data we get:

BA58 = 012010

12 Dec 2009 = 122009 = 012010 = FALSE
14 Jan 2010 = 012010 = 012010 = TRUE
12 Feb 2010 = 022010 = 012010 = FALSE
12 Mar 2010 = 032010 = 012010 = FALSE
12 Apr 2010 = 042010 = 012010 = FALSE
12 May 2010 = 052010 = 012010 = FALSE
etc
etc

We need to convert those logicals to numbers. One way to do that is to use 
the double unary minus --.

--TRUE = 1
--FALSE = 0

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

Then we get an array of 1s or 0s:

12 Dec 2009 = 122009 = 012010 = --FALSE = 0
14 Jan 2010 = 012010 = 012010 = --TRUE = 1
12 Feb 2010 = 022010 = 012010 = --FALSE = 0
12 Mar 2010 = 032010 = 012010 = --FALSE = 0
12 Apr 2010 = 042010 = 012010 = --FALSE = 0
12 May 2010 = 052010 = 012010 = --FALSE = 0
etc
etc

Now we have 2 arrays of numbers, the array of 1s and 0s and the array of 
numbers in the range AR59:AR2000. Those arrays might look something like 
this:

0...10
1...14
0...0
0...7
0...22
0...19
etc
etc

The arrays are multiplied together:

0*10 = 0
1*14 = 14
0*0 = 0
0*7 = 0
0*22 = 0
0*19 = 0
etc
etc

SUMPRODUCT then sums the results of that multiplication:

SUMPRODUCT({0;14;0;0;0;0}) = 14

So, lookup "012010" in the range of dates AP59:AP2000 and return the 
corresponding numeric value from AR59:AR2000:

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

=14

For more on SUMPRODUCT see this:

http://xldynamic.com/source/xld.SUMPRODUCT.html

-- 
Biff
Microsoft Excel MVP


"GBExcel via OfficeKB.com" <u55438@uwe> wrote in message 
news:a275058b31ae0@uwe...
> Thank you. Will try it.
>
> The double minus (as in "--") is new to me. What does it do?
>
> GBExcel
>
> T. Valko wrote:
>>Try this...
>>
>>=SUMPRODUCT(--(TEXT(AP59:AP2000,"mmyyyy")=BA58),AR59:AR2000)
>>
>>> >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.aspx/excel-functions/201001/1
> 


0
T
1/21/2010 10:17:12 PM
Thank you,

You've gone more than the extra mile.

Appreciate it.

GBExcel

-- 
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-functions/201001/1

0
GBExcel
1/21/2010 10:50:29 PM
You're welcome. Thanks for the feedback!

-- 
Biff
Microsoft Excel MVP


"GBExcel via OfficeKB.com" <u55438@uwe> wrote in message 
news:a2766573e1fb8@uwe...
> Thank you,
>
> You've gone more than the extra mile.
>
> Appreciate it.
>
> GBExcel
>
> -- 
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.aspx/excel-functions/201001/1
> 


0
T
1/21/2010 11:20:48 PM
Reply:

Similar Artilces:

HELP!! #2
Does anyone know how to Synch AIM mail to outlook? ...

convert Date formate (windows default) to Arabic
Dear all, any one have an idea about how i can convert the Date from gorgian Date formate (windows default) to Arabic Um AL Qura format (Hijiri) ? -- MBS Developer Abdelrahman Nabil ...

Need to recall an invoice
Hullo. We have a user who accidentally deleted an invoice and would like it recalled. Is there a simple fix for this or do I need to call in some big guns? Thanks, Mike I take it the invoice was NOT posted? Was it transferred from an Order or keyed in? -- Richard L. Whaley Author / Consultant / MVP Documentation for Software Users Get our Free Tips and Tricks Newsletter and check out our books at http://www.AccoladePublications.com "Mike" wrote: > Hullo. We have a user who accidentally deleted an invoice and would > like it recalled. Is there a simple fix for thi...

money 2007 vs. providian ? please help
money wont retrieve info from providian. if i look up providian in the ms money compatibility link as posted by others I get 'direct services' what gives any help? In microsoft.public.money, newby wrote: >money wont retrieve info from providian. > >if i look up providian in the ms money compatibility link as posted by >others I get 'direct services' I don't think so. It looks like "third party". > >what gives > >any help? ...

Dates in XIRR
I was just looking at http://msdn2.microsoft.com/en-us/library/bb224771.aspx, which states, in reference to the XIRR function: "Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text." Is this something new in Excel 2007? I've never encountered such a problem with earlier versions of Excel On Apr 27, 3:07 pm, "Howard Kaikow" <kai...@standards.com> wrote: > Is this something new in Excel 2007? No. The H...

Bar Graph Help
I'm trying to create a graph similar to the bar graph shown in the middle of the dashboard in the link below labeled as "YTD % of target", but don't know how to get the overlapping bars in excel. Can anyone advise? Thanks! http://www.bonavistasystems.com/Download2/Airline%20Dashboard2.png Here's one way to do it... Add the YTD % series to your bar graph, and then format the series so that it is on the secondary axis. XL will probably create a secondary X-axis, but that will cause a distortion of the data. We need them to be plotted on the same x-ax...

how do i change the color of a cell auotmatically?
I am desiging a data shee and i want to changes the color of a cell based on the valuse of anthor cell. ex. if "cell m34" is equal to 0 then "cell p21" is white, if cell m34 less than 1548, then "cell P21" is yellow Can this be done? If so, please help me out. Hi! Select cell P21 Goto Format>Conditional Formatting In the dropdown select Formula is Enter this formula in the box: =AND(M34<>"",M34=0) Click the Format button and select the style to apply then click OK. Click the Add button In Condition 2 Formula is: =AND(M34>=1,M34<=154...

Is it possible to have text and currancy in one cell and ...
Is it possible to have text and currancy in one cell and be able to use the numbers in that cell to make an equation with another? The short answer .. No -- Rgds Max xl 97 --- Please respond in thread xdemechanik <at>yahoo<dot>com ---- "thegen" <thegen@discussions.microsoft.com> wrote in message news:1552FF10-9CAA-42AD-B401-B0D229D6A343@microsoft.com... > Is it possible to have text and currancy in one cell and be able to use the > numbers in that cell to make an equation with another? well maybe depending on how the "text & currency" was ar...

How to skip cells with text strings.
What should I add to the code below to cause it to skip a cell tha contains a text string in the cell above it? I need it to only work o numbers in the cell above it. If ActiveCell.Offset(rowoffset:=-1) <> "" Then ActiveCell = ActiveCell.Offset(rowoffset:=-1) + 1 Application.SendKeys ("{Enter}") End If Thanks so much. mikebur -- mikebur ----------------------------------------------------------------------- mikeburg's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2458 View this thread: http://www.excelforum.com/showthread.php?thread...

Diffrent cell on diffrent date?
I�m going to TRY and explain this the best I can. For work when we do our cash out at the end of the night we use a exce sheet. This sheet is to add up our change, checks, credit cards etc etc. etc. When we're done we print it out and than type the figure into another excel sheet. I pretty much know how to combine the sheets so instead of typing the numbers into SS1 and than entering i into SS2 I can have the numbers going from SS1 to SS2 automatically But my question is that how do I set it up so when I type the info i SS1 that it will go to in to SS2 but to have the cells change by dat...

Excel Formula Guidance. Formula need to determine if cell is popul
Hi, Hopefully someone will be able to provide some guidance. I have been tasked with setting up some spreadsheets at work to application coming into my department and the number of days it takes for us to deal with them. To work this out is easy enough using formula =NETWORKDAYS(B4,E4,$A$119:$A$158) with the numbers involved with the $ referencing bank holidays listed in the spreadsheet and B4 received date and E4 being the completed date. I now need to include some extra lines as I need to be able to monitor written enquiries as well so the headings would read Application ...

Help Downloading OS to Target
Hi all. Kind of new to CE. I have managed to build a CEPC OS and have tested it on MS Virtual Machine. Problem I am having is trying to download it to the CEPC. From everything I can find, I need to have a 1.44 boot disk. Neither of my machines have a floppy drive. Ultimately, an ISO that can be burned to CD would be great! Any help would be greatly appreciated. Thank you. That depends on the media that your BIOS supports for booting -- Bruce Eitman (eMVP) Senior Engineer Bruce.Eitman AT Eurotech DOT com My BLOG http://geekswithblogs.net/bruceeitman Eurotech Inc....

Coping numbers to next blank cell on another worksheet
I am just learning Excel (need SIMPLE help) but any help would be greatly appreciated. My OS is XL Professional and I have MS Excel 2002 SP2. I need to copy calculated numbers (I got this much done on my own) in specific cells on worksheet # 1 to the next blank cells in existing columns of numbers used in calculations on other worksheets in the same workbook. I other words, I have a formula that calculates numbers and places the results in the same column and row each time it is used. Those calculated numbers need to be copied and used on different worksheets in the same workbook. While...

Split combined date time data #2
From file dump have combined date time cells eg "14/04/03 14:20" (value 37725.59722). Want to perform time analysis so need to split to 2 separate cells; eg. "14/04/2003" (value 37725) & "14:20" (value 0.59722). Is there a fnc to do this? (Currently convert cell to value, then fncs trunc & cell less int(cell) then refmt to date dd/mm/yy & time hh:mm respectively) See one answer in reply to your slightly later post. In article <EDD7065A-E5AA-40AB-BF38-50707E04109B@microsoft.com>, "Mark Ada" <MarkAda@discussions.microsoft.co...

Pivot Table Help #2
I have a sheet that I use to keep track daily of the value of inventory by day, and grouped by week. The value of the inventory is tracked by three different subgroups. (Below is an example). On my piviot table the values are grouped by type and by week. So what I get is a five day average of each Type by week, which is exactly what I want. My averages are as follows: M $796091.00 O $615069.00 R $3180669.00 =================== Grand Totals $1530609.00 The grand totals are where I need help. The grand total is an average of the avaerages. Is it possible to display a sum of the av...

dates in queries
I am using access 2007 and am having trouble filtering dates in my queries. I have a qry with multiple date fields. However I am only narrowing the criteria in the [discharge date] field by using >= [discharge date]. When I enter 10/01/2007 I get 40 entries from september. What am I doing wrong? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200710/1 Does 10/01/2007 mean October 1, 2007 to you, or does it mean January 10, 2007? When you type 10/01/2007, Jet (ACCESS) thinks you mean October 1, 2007, because it's programmed...

How to save custom cell formatting?
When I goto Format - Cells - Custom and type my own format. For e.g. #,###,##0.00000 The format mentioned above is available for current file only. How do I make it available to other files (and new ones) as well? ...

Need to access selective cells from Excel file stored at Sharepoin
I need to link my PPT 2007 slide with an Excel 2003/2007 sheet data stored at Sharepoint 2007. Excel sheet is having lots of cells/rows - hundreds, but i want only selective ranges to be visible in PPT slide. Moreover, if can apply some calculations prior to rendering the date, is also required. Any suggestions please? ...

how do i copy rows to a new worksheet by a cell value
Hi, I have a worksheet with many rows of values. Where a cell in a particular column is "Yes" I wish to copy the values of that row to a different worksheet. Is this something suited to a macro? Many thanks for any assistance. Nibbs without a macro, use data>filter>autofilter>filter on yes>copy -- Don Guillett SalesAid Software dguillett1@austin.rr.com "Nibbs" <Nibbs@discussions.microsoft.com> wrote in message news:144D7242-BAFC-43B4-98EA-EC95657832B2@microsoft.com... > Hi, > > I have a worksheet with many rows of values. Where a cell ...

Calculate date based on other table
Below are my 2 tables:- Table1 ItemID (primary key) Days Table 2 Serial number(primary key) ItemID dateExpired I need the dateExpired to be auto calculated when I enter the ItemID. The dateExpired is today's date + the days in the table 1...May I know the way? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200705/1 It would be a good idea to create an index for the ItemID field in Table 2 You did not say whether there is a form involved of if you are doing recordset processing or if this is being done in a query. Where you...

loop thru cells to find matching record
I'm having difficulty with the following code. I think thre are several issues, perhaps the easier one first. When I set the following range, I noticed it didn't start at row 14 but at row 12, not sure why? Set rng = wsSheet.Range("A14:A" & Cells(65536, "A").End(xlUp).Row). I noticed that the message box shows cells I'm not interested in. The more difficult problem is per the following The active workbook, "sheet 1" has a lising of serial numbers, the serial numbers start at row 14, the exact number varies, hence the set rng as per abo...

look one cell below
Does anyone know how reference one cell below another cell. Example In cell B10 I want to return the value in the cell below cell A5, but I can't type A6 directly. I want to type in something like =A(5+1) but excel doesn't take that. Any suggestions? Thanks! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Hi, try =OFFSET(A5,1,0) Gromit ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post u...

Really Need Some Help Here
RMS V 1.3.1006 We have a closed location. We do not have their database (this was our first closed location - 1 yr ago). This location has inventory that resides on it. Their computer has been retasked, and wiped clean for the new location. We have reports that pull that location's inventory. That is bad. I have worked around the RMS reports to add filter qualities, like Store Region <> Closed, etc. However, now we are also using a separate reporting tool that does not function like this. Does anyone (please help me!) know a query that can be run from the HQ side to compl...

MS Query Enetreing a date as Parameter
I'm trying to build a MS Query to access a quickbooks database using dates as parameters. So I enter [Enter Beginning Date] in the criteria and then 12/12/05 as my date when prompted which then gets either a "syntex error" or and "failure in conversion" error? What format should I use? ...

Manual Input of Cell Refs
I need to do a calc on sheet 1 which will use data from sheet 2. I want to point to the row on sheet 2 by manually inputting its number in sheet 1 at ,say, A1. Ex: In sheet 1 a3 I have the formula =Sum(sheet2!A?:G?) where the value of ? has been inserted manually in Sheet1 at A1 TIA Craig Hi try = =Sum(INDIRECT("'sheet2'!A" & A1 & ":G" & A1)) -- Regards Frank Kabel Frankfurt, Germany Craig wrote: > I need to do a calc on sheet 1 which will use data from sheet 2. > I want to point to the row on sheet 2 by manually inputting...