Formula in excel

[claude jerry]

I have Following Date in sheet1
Sheet1
ColA ColB ColC ColD
Room ChqNo ChqDt Amt
1 11551 01-06-09 500
2 25516 01-07-09 300
3 55159 01-08-09 250
1 11552 01-07-09 500
4 88192 01-08-09 300
2 25517 01-08-09 200
3 55160 01-11-09 200
1 11553 01-11-09 200
3 55161 01-12-09 200
4 88192 01-12-06 200

In Sheet 2 I Have following
Room Chq1 Chq2 Chq3 Chq4 Chq5
1
2
3
4

I need a formula to get the cheque amt in respective room
Final result should look this way
Room Chq1 Chq2 Chq3 Chq4 Chq5
1 500 500 300 0 0
2 300 200 0 0 0
3 250 200 200 0 0
4 300 200 0 0 0

 

[Max]

One play which delivers it ..
(disregarding some inconsistencies in your description due to typos)

In Sheet1,
In F2: =IF($A2="","",IF($A2=COLUMNS($A:A),ROW(),""))
Copy F2 across by 4 cols to I2 (since you have 4 rooms), then fill down as
far as required to cover the max expected extent of source data

In Sheet2,
In B2:
=IF(COLUMNS($A:A)>COUNT(OFFSET(Sheet1!$E:$E,,ROWS($1:1))),"",INDEX(Sheet1!$D:$D,SMALL(OFFSET(Sheet1!$E:$E,,ROWS($1:1)),COLUMNS($A:A))))
Copy B2 across/fill down as far as required to return the desired results,
all neatly packed to the left.

And if you want to extract the corresponding chq numbers (from col B in
Sheet1), just make a copy of Sheet2, then change the INDEX part of the
formula in B2 to point to Sheet1's col B instead, ie change to: ...
INDEX(Sheet1!$B:$B, ...
Then re-copy B2 across/fill down

Above any good? Hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik