complicated calculations
 

Hi,

I want to automate a manual cash flow projection process in excel.
Currently, the daily cash receipts forecast is manually keyed in. I want to
automate this and have extracted data from our accounting system complete
with customer number, due date and value in sheet1. The number of customers
will depend on the value outstanding at any point in time. I then have
another spreadsheet (sheet2) within the same workbook with all customers
listed with their account numbers and the week days for the next three months
across the top. What I need is a formula that looks at the account number in
sheet 2, matches it to sheet 1 and then checks the due date at the top of the
page in sheet 2, matches it if it can to sheet 1 and picks up the value.
Eg...

Sheet1

Customer Due Date Value
CustomerA 03/09/06 1200.00
CustomerB 01/09/06 2500.00
CustomerB 03/09/06 1810.00
SheetB
01/09/06 02/09/06 03/09/06 04/09/06
CustomerA 0.00 0.00 1200.00 0.00
CustomerB 2500.00 0.00 1810.00 0.00

Any ideas?

Chris
--
Chris

complicated calculations
 

In B2 on Sheet2,

=IF(ISNA(MATCH(1,(Sheet2!$A2=Sheet1!$A$1:$A$4)*(B$ 1=Sheet1!$B$1:$B$4),0)),0,
INDEX(Sheet1!$C$1:$C$4,MATCH(1,(Sheet2!$A2=Sheet1! $A$1:$A$4)*(B$1=Sheet1!$B$
1:$B$4),0)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

Copy down and across.

--
HTH

Bob Phillips

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

"christinac" wrote in message
...
Hi,

I want to automate a manual cash flow projection process in excel.
Currently, the daily cash receipts forecast is manually keyed in. I want
to
automate this and have extracted data from our accounting system complete
with customer number, due date and value in sheet1. The number of
customers
will depend on the value outstanding at any point in time. I then have
another spreadsheet (sheet2) within the same workbook with all customers
listed with their account numbers and the week days for the next three
months
across the top. What I need is a formula that looks at the account number
in
sheet 2, matches it to sheet 1 and then checks the due date at the top of
the
page in sheet 2, matches it if it can to sheet 1 and picks up the value.
Eg...

Sheet1

Customer Due Date Value
CustomerA 03/09/06 1200.00
CustomerB 01/09/06 2500.00
CustomerB 03/09/06 1810.00
SheetB
01/09/06 02/09/06 03/09/06 04/09/06
CustomerA 0.00 0.00 1200.00 0.00
CustomerB 2500.00 0.00 1810.00 0.00

Any ideas?

Chris
--
Chris

complicated calculations
 

That works perfectly! Thanks Bob
--
Chris


"Bob Phillips" wrote:

In B2 on Sheet2,

=IF(ISNA(MATCH(1,(Sheet2!$A2=Sheet1!$A$1:$A$4)*(B$ 1=Sheet1!$B$1:$B$4),0)),0,
INDEX(Sheet1!$C$1:$C$4,MATCH(1,(Sheet2!$A2=Sheet1! $A$1:$A$4)*(B$1=Sheet1!$B$
1:$B$4),0)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

Copy down and across.

--
HTH

Bob Phillips

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

"christinac" wrote in message
...
Hi,

I want to automate a manual cash flow projection process in excel.
Currently, the daily cash receipts forecast is manually keyed in. I want
to
automate this and have extracted data from our accounting system complete
with customer number, due date and value in sheet1. The number of
customers
will depend on the value outstanding at any point in time. I then have
another spreadsheet (sheet2) within the same workbook with all customers
listed with their account numbers and the week days for the next three
months
across the top. What I need is a formula that looks at the account number
in
sheet 2, matches it to sheet 1 and then checks the due date at the top of
the
page in sheet 2, matches it if it can to sheet 1 and picks up the value.
Eg...

Sheet1

Customer Due Date Value
CustomerA 03/09/06 1200.00
CustomerB 01/09/06 2500.00
CustomerB 03/09/06 1810.00
SheetB
01/09/06 02/09/06 03/09/06 04/09/06
CustomerA 0.00 0.00 1200.00 0.00
CustomerB 2500.00 0.00 1810.00 0.00

Any ideas?

Chris
--
Chris