Find category value based on date range?

[thewiz1972]

Here's the problem:

I have several worksheets, split out by month, with a list of dates, a description and a value.

A1 A2 A3
1/13/2005 Bill1 ?Value
1/15/2005 Bill2 ?Value
1/15/2005 Bill3 ?Value

Etc.

In another worksheet I have a list a bills, their value, and the date range for which they are valid

Bill Name StartDate EndDate Value
Bill1 1/1/2005 2/28/2005 $100.00
Bill1 3/1/2005 4/30/2005 $90.00
Bill2 1/1/2005 12/31/2005 $50.00
Bill3 1/1/2005 6/30/2005 $100.00
Bill3 7/1/2005 12/31/2005 $75.00

What I need to do is find the value of bill x (A2) in the worksheet with the list of bills, date ranges and values, based on the date value in A1 so that column A3 can be populated with the result.

Any help would be greatly appreciated.


--------------= Posted using GrabIt =----------------
------= Binary Usenet downloading made easy =---------
-= Get GrabIt for free from http://www.shemes.com/ =-

 

[Guest]

You may use a formula like this on Sheet1 (cell C2)
=SUMPRODUCT(--(Sheet2!$A$2:$A$6=B2),--(Sheet2!$B$2:$B$6<A2),--(Sheet2!$C$2:$C$6>A2),Sheet2!$D$2:$D$6)

Change the Sheet2 ranges to reflect your data
Hope this helps,
Miguel.

 

[thewiz1972]

That worked great. Thanks so much.

One question tho. What is the function of the '--' before the first
three array definitions?

-Mark

 

[Guest]

It ensures that the TRUE/FALSE values from the comparisions are treated as
1/0. The first - makes it a number (-1 or 0), and the second one change the
sign again, to the desired 1 or 0

Miguel.

 

[thewiz1972]

Very cool. Perhaps you could answer one more question for me.
In Sheet2 I will be adding new bills from time to time so is there a
way to properly reference this dynamic range in the formula you've
provided?

Thanks,
-Mark

 

[Guest]

Sure, I use to recommend this link, it has good examples and explanations:
http://www.ozgrid.com/Excel/DynamicRanges.htm

Miguel.