complicated calculations

[Guest]

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

 

[Bob Phillips]

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)

 

[Guest]

That works perfectly! Thanks Bob
--
Chris

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)