Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF-THEN Formula help needed | Excel Worksheet Functions | |||
continuous sum formula needed | Excel Discussion (Misc queries) | |||
Formula Help Needed | Excel Discussion (Misc queries) | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Help! Formula needed. | Excel Discussion (Misc queries) |