Excel 2002: How to fill up data in running order?

[Guest]

Dear Sir

I have Table 1 with column A and B empty initially. The Data in Table 2 is
the data for each subtotal in Table 1. The sequence of the data has been
sorted accordingly and is in running order.

May I know what formula I must input at Cell A1 and B1 and copy down to get
the answer as illustrated below.

< There is no common reference for lookup between Table 1 and 2>


Thanks

Low


Table 1

A B C D E
1 DJ201062 06/09/2001 IV791667 RT158674 1,000
2 DJ201062 06/09/2001 IV791666 RT158674 1,000
3 DJ201062 06/09/2001 IV790692 RT158674 500
4 TOTAL : 2,500
5 DJ201071 05/12/2001 IV796284 RT158836 3,609
6 TOTAL : 3,609
7 DJ201105 25/08/2001 CN025037 RT158465 26
8 DJ201105 25/08/2001 RD864412 RT158465 0
9 TOTAL : 26
10 DJ201126 29/09/2001 CN025017 RT158070 102
11 DJ201126 29/09/2001 RD861084 RT158070 0
12 TOTAL : 102
13 DJ201128 02/08/2001 CN024979 RT15806 17
14 DJ201128 02/08/2001 RD861025 RT1580 0
15 TOTAL : 17



Table 2

21 DOC Date AH Code
22 DJ201062 06/09/2001 PKHLN014
23 DJ201071 05/12/2001 PKHLU068
24 DJ201105 25/08/2001 PKHLM022
25 DJ201126 29/09/2001 PKHLI005
26 DJ201128 02/08/2001 PKHLP541
27 DJ201129 02/08/2001 PKHLZ999
28 DJ201130 02/08/2001 PKHLS639

 

[Guest]

One way ..

Put in A1:
=IF($D1="TOTAL :","",OFFSET($A$22,COUNTIF($D$1:$D1,"TOTAL :"),COLUMN(A1)-1))
Copy A1 to B1, fill down to B15. Format B1:B15 as dates.

 

[Guest]

Hello Max,

Many thanks for your formula.

It works perfectly well.


Kind Regards

Low

 

[Max]

pleasure`, Low.