B
barbetta3141
In one worksheet, I have a list of payments, e.g....
Date, Amount, Description
6/1/06, $400.00
6/15/06, $300.00
7/11/06, $700.00
7/12/06, $120.00
On another worksheet I want a cashflow schedule based on the first
Monday of each week, e.g...
Week beginning, Cash In, Cash Out
5/29/06, $400, -$250
6/5/06, $0, -$250
6/12/06, $300, -$250
Unfortunately, the lookup formula for the Cash In column is not as
simple as =VLOOKUP(A2,PaymentSchedule,2,TRUE).
Two problems: (1) for the Cash In column, can I use VLOOKUP to find not
just payments on that day, but the whole week (e.g. payments between
5/29/06 and 6/4/06)? (2) what if there is more than one payment in a
week, can I sum them?
If this is possible, I am thinking (1) will involve an array formula
and (2) will use SUMPRODUCT (or array SUMIF), but I don't know how to
set up the formula. Thanks for any suggestions.
Date, Amount, Description
6/1/06, $400.00
6/15/06, $300.00
7/11/06, $700.00
7/12/06, $120.00
On another worksheet I want a cashflow schedule based on the first
Monday of each week, e.g...
Week beginning, Cash In, Cash Out
5/29/06, $400, -$250
6/5/06, $0, -$250
6/12/06, $300, -$250
Unfortunately, the lookup formula for the Cash In column is not as
simple as =VLOOKUP(A2,PaymentSchedule,2,TRUE).
Two problems: (1) for the Cash In column, can I use VLOOKUP to find not
just payments on that day, but the whole week (e.g. payments between
5/29/06 and 6/4/06)? (2) what if there is more than one payment in a
week, can I sum them?
If this is possible, I am thinking (1) will involve an array formula
and (2) will use SUMPRODUCT (or array SUMIF), but I don't know how to
set up the formula. Thanks for any suggestions.