Aquiring data from one sheet to another sheet in the same workbook

[No News]

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

 

[Guest]

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(SMALL($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

 

[Guest]

Clarification: The key col is assumed to be col A in sheet: X (PO numbers)

---

 

[No News]

Dear Max.

Noted your two replies. Thanks for your immediate reply.

Will come to you after checking, if need further clarifications.

TT

 

[Guest]

You're welcome.
Thanks for callback ..

 

[No News]

Dear Max.

All worked well. Thanks.

But if I insert a data in a particular row, it will not be updated.

For your info, I have around 15 Customers. I have one column for entering
the name of he customer and currently I am using Autofilter option and
subtotal options.

Please help to fix it.

Thanks in advance.
TT

 

[Guest]

All worked well. Thanks.
But if I insert a data in a particular row, it will not be updated.

Inserting rows or deleting rows within the assumed data area in the source
sheet: Y, ie within rows 2 - 2000 will upset things in X. If you cannot help
this happening in daily ops, just restore things in sheet: X to working order
by re-filling the top row formulas in Y's A2:C2 down to C2000 (In Y, select
A2:C2, copy down to C2000).

For your info, I have around 15 Customers. I have one column for entering
the name of the customer and currently I am using Autofilter option and
subtotal options.

If the cust name is in a col to the right, say in col E in sheet: X, just
extend the formula in Y's E2 to cover by copying across to I2, then fill down
to I51. This will bring in the cust name details in col I.


---

 

[Guest]

sorry, typo in line

... just restore things in sheet: X to working order ..

should read as:

... just restore things in sheet: Y to working order ..

---

 

[Guest]

Apologies, there were numerous typos within the entire para:

Inserting rows or deleting rows within the assumed data area in the source
sheet: Y, ie within rows 2 - 2000 will upset things in X. If you cannot help
this happening in daily ops, just restore things in sheet: X to working order
by re-filling the top row formulas in Y's A2:C2 down to C2000 (In Y, select
A2:C2, copy down to C2000).

Para should have read as:

Inserting rows or deleting rows within the assumed data area in the source
sheet: X, ie within rows 2 - 2000 will upset things in Y. If you cannot help
this happening in daily ops, just restore things in sheet: Y to working order
by re-filling the top row formulas in Y's A2:C2 down to C2000 (In Y, select
A2:C2, copy down to C2000).

---

 

[No News]

Dear Max.

Thanks for all your help and sincere reply.

Great. All that works fine.

But for me, not that much experienced in excel, it takes two days to
understand and make a trial. Now it is perfect. Welldone.

Thanks a lot for your help.

TT

 

[Guest]

Glad to hear that, TT !
Thanks for posting back ..