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

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

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
--
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
>
>
>
>
>

Clarification: The key col is assumed to be col A in sheet: X (PO numbers)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Dear Max.

Noted your two replies. Thanks for your immediate reply.

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

TT

"Max" <demechanik@yahoo.com> wrote in message
news:B52B6FDE-2955-471F-8846-444425B4B7F7@microsoft.com...
> Clarification: The key col is assumed to be col A in sheet: X (PO numbers)
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---

You're welcome.
Thanks for callback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"No News" wrote:
> Dear Max.
> Noted your two replies. Thanks for your immediate reply.
> Will come to you after checking, if need further clarifications.
> TT

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




"Max" <demechanik@yahoo.com> wrote in message
news:753A75AA-AE0A-429B-8E78-E11189DFF4B6@microsoft.com...
> You're welcome.
> Thanks for callback ..
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
> "No News" wrote:
> > Dear Max.
> > Noted your two replies. Thanks for your immediate reply.
> > Will come to you after checking, if need further clarifications.
> > TT

"No News" wrote:
> 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.

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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

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).

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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


"Max" <demechanik@yahoo.com> wrote in message
news:377C69D8-CB0E-4FD2-A3DB-1C6E7F80B0BB@microsoft.com...
> 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).
>
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---

Glad to hear that, TT !
Thanks for posting back ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"No News" wrote:
> 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