Here's a model that delivers precisely what you're after ..
http://www.savefile.com/files/2239744
Direct Filter from another sheet.xls
Assuming source data in sheet: X,
cols A to E, data in row2 down
In another sheet: Y (say), place
In A2:
=IF(X!A2="","",IF(COUNTIF(X!$A$2:A2,X!A2)1,"",ROW ()))
In B2:
=IF(ROW(A1)COUNT(A:A),"",INDEX(X!A:A,MATCH(SMALL( A:A,ROW(A1)),A:A,0)))
In C2:
=IF(X!A2="","",IF(X!A2=$D$1,ROW(),""))
Select A2:C2, copy down as far as required to cover the max expected extent
of data in the key col A in X, say down to C2000
(Hide away cols A to C, or just format the font in white to mask)
Click Insert Name Define, input:
Names in workbook: PO_Num
Refers to: =OFFSET(Y!$B$2,,,SUMPRODUCT(--(Y!$B$2:$B$2000<"")))
Click OK
Then select D1, click Data Validation, Allow: List, Source: =PO_Num
D1 will now yield a selectable dropdown of unique PO#'s from the key col A
in X
Paste the same col headers in X into E1:H1
Then place in E2:
=IF(ROW(A1)COUNT($C:$C),"",INDEX(X!A:A,MATCH(SMAL L($C:$C,ROW(A1)),$C:$C,0)))
Copy E2 to H2, fill down by the smallest range sufficient to cover the max
expected number of lines for any single PO, say to H51 (if max lines per PO =
50)
Test it out, select a PO# from the droplist in D1
All relevant lines for that PO will appear neatly bunched at the top within
cols E to H
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"No News" wrote:
Hi All
I have a work sheet with the datas as below
A B C D
E ------- so on.
1 P.O.# STYLE# ORD.QTY PRICE VALUE ............ etc...
2 P-150 ABT-5 500 $2.50
$1250.00.............etc
3
4
5
6
....
....
.....
goes on....
I have more than 1000 entries in an year
Now I need the following to be worked out in the next sheet
A B
1 P.O.# Here I have to select the P.O Nos what is entered
in the data sheet
2 INVOICE NO Here the relevant Invoice No. for what I selected as above
should apper
3 VALUE Same as above
6 AMT RECD
8 BALANCE TO RECEIVE
Can any one help me please
TT