Formula help needed !
 

Hi

Workbook contains 2 worksheets.

Worksheet 1:

Column A - contains a list of dates going down in ascending order.
Column B - contains a numeric value for the adjacent date.

eg:
12/05/2005 // 125.25
13/05/2005 // 252.62

Worksheet 2:

User enters a date into cell A1

I need a formula in cell B1 that will look at the date entered into
cell A1, find the corresponding value for it on worksheet 1, and then
find the total of that value PLUS all values before it (up to a maximum
of 4 rows above).

So, if the date entered = 06/05/2005, and worksheet 1 looked like
this:

01/05/2005 // 12.53
02/05/2005 // 14.26
03/05/2005 // 125.26
04/05/2005 // 14.24
05/05/2005 // 22.25
06/05/2005 // 31.31
07/05/2005 // 112.68

I'd want the SUM of all cells from B2 to B6 (14.26 31.31)

Anyone help on this one?

Thanks


--
thegooner
------------------------------------------------------------------------
thegooner's Profile: http://www.excelforum.com/member.php...o&userid=36618
View this thread: http://www.excelforum.com/showthread...hreadid=565078

Formula help needed !
 

=SUM(INDEX(Sheet1!B1:B20,MAX(1,MATCH(A1,Sheet1!A1: A20,0)-4)):INDEX(Sheet1!B1
:B20,MATCH(A1,Sheet1!A1:A20,0)))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"thegooner" wrote
in message ...

Hi

Workbook contains 2 worksheets.

Worksheet 1:

Column A - contains a list of dates going down in ascending order.
Column B - contains a numeric value for the adjacent date.

eg:
12/05/2005 // 125.25
13/05/2005 // 252.62

Worksheet 2:

User enters a date into cell A1

I need a formula in cell B1 that will look at the date entered into
cell A1, find the corresponding value for it on worksheet 1, and then
find the total of that value PLUS all values before it (up to a maximum
of 4 rows above).

So, if the date entered = 06/05/2005, and worksheet 1 looked like
this:

01/05/2005 // 12.53
02/05/2005 // 14.26
03/05/2005 // 125.26
04/05/2005 // 14.24
05/05/2005 // 22.25
06/05/2005 // 31.31
07/05/2005 // 112.68

I'd want the SUM of all cells from B2 to B6 (14.26 31.31)

Anyone help on this one?

Thanks


--
thegooner
------------------------------------------------------------------------
thegooner's Profile:
http://www.excelforum.com/member.php...o&userid=36618
View this thread: http://www.excelforum.com/showthread...hreadid=565078