Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Aquiring data from one sheet to another sheet in the same workbook
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Aquiring data from one sheet to another sheet in the same work
Clarification: The key col is assumed to be col A in sheet: X (PO numbers)
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Aquiring data from one sheet to another sheet in the same work
Dear Max.
Noted your two replies. Thanks for your immediate reply. Will come to you after checking, if need further clarifications. TT "Max" wrote in message ... Clarification: The key col is assumed to be col A in sheet: X (PO numbers) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Aquiring data from one sheet to another sheet in the same work
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Aquiring data from one sheet to another sheet in the same work
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" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Aquiring data from one sheet to another sheet in the same work
"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 --- |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Aquiring data from one sheet to another sheet in the same work
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 --- |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Aquiring data from one sheet to another sheet in the same work
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 --- |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Aquiring data from one sheet to another sheet in the same work
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" wrote in message ... 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 --- |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Aquiring data from one sheet to another sheet in the same work
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing unopened worksheet into an open Workbook | Excel Discussion (Misc queries) | |||
Inputting data to one worksheet for it effect another | Excel Discussion (Misc queries) | |||
Importing Data | Excel Worksheet Functions | |||
Inserting Filtered RC cell information into other worksheets | Excel Discussion (Misc queries) | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions |