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

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









--
A36B58K641

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

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Mr. Low" wrote:
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









--
A36B58K641

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

Hello Max,

Many thanks for your formula.

It works perfectly well.


Kind Regards

Low

--
A36B58K641


"Max" wrote:

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Mr. Low" wrote:
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









--
A36B58K641

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

pleasure`, Low.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Mr. Low" wrote in message
...
Hello Max,

Many thanks for your formula.

It works perfectly well.


Kind Regards

Low