Try this
Sheet 1
Invoice Debtor Amt
IV004 ABC 140
IV002 BQE 0
IV001 DEF 60
IV003 ABC 0
IV005 ABC 20

Sheet 2
Invoice Date
IV004 02/05/2007
IV002 09/07/2007
IV001 03/11/2007
IV003 02/08/2007
IV005 11/09/2007

Sheet 3
Debtor Invoice Date Age Amt
ABC IV004 02/05/2007 236 140
DEF IV001 03/11/2007 51 60
ABC IV005 11/09/2007 104 20

sheet 3!
A2=IF(ROWS(Sheet1!$1:1)<=COUNTIF(Sheet1!$C$2:$C$10 ,"0"),INDEX(Sheet1!$B$2:$B$10,SMALL(IF(Sheet1!$C$ 2:$C$100,ROW(Sheet1!$B$2:$B$10)-MIN(ROW(Sheet1!$B$2:$B$10))+1),ROWS(Sheet1!$1:1))) ,"")

B2=iF(ROWS(Sheet1!$1:1)<=COUNTIF(Sheet1!$C$2:$C$10 ,"0"),INDEX(Sheet1!$A$2:$A$10,SMALL(IF(Sheet1!$C$ 2:$C$100,ROW(Sheet1!$B$2:$B$10)-MIN(ROW(Sheet1!$B$2:$B$10))+1),ROWS(Sheet1!$1:1))) ,"")
"aw" wrote:

C2
=SUMPRODUCT(--(Sheet1!$B$2:$B$6=$A2)*--(Sheet1!$A$2:$A$6=$B2),Sheet2!$B$2:$B$6)

E2=SUMPRODUCT(--(Sheet1!$B$2:$B$6=$A2)*--(Sheet1!$A$2:$A$6=$B2),Sheet1!$C$2:$C$6)
A2, B2 are array forumlas. You have to enter by ctrl+shift+enter not just
enter.
The result is not on "age" field. Hope somebody will give a better way.
with regards
sridhar

Existing I have file A & B.

File A is the summary (pivot table summary from other sources)
File B is the invoices master to store all invoice information.

What I hope to obtain is to generate file C (listed below).

3 Criteria needs :
a). File C should select only data from file A for outstanding amt not
equal to zero (0).
b). File Cs information should be refreshable based on information from
file A & B stored in diff. location.
c). Field €œAge (days) €“ calculation field€� is equal to number of days
outstanding. i.e. today() €“ inv date

As this report should be generated daily, I dont want to prepare this file
manually.

I am now thinking SQL + pivot table + macro to perform this task. Could you
please let me some hints / ways to solve this problem.

Thanks a lot!!

======================================
File A (Amt outstanding)

invoice debtor outstanding amt
IV004 ABC 140
IV002 BQE 0
IV001 DEF 60
IV003 ABC 0
IV005 ABC 20

File B (invoice master)

invoice inv date
IV004 2007/05/02
IV002 2007/07/09
IV001 2007/11/03
IV003 2007/08/02
IV005 2007/09/11


File C (merge from file A & B)

debtor invoice inv date AGE (days) outstanding amt
ABC IV004 2007/05/02 236 140
ABC IV005 2007/09/11 104 20
DEF IV001 2007/11/03 51 60








--
aw