Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatic populate Sheet2 with data from Sheet1 based on criteria.
Hello, I have two Worksheets Sheet1 and Sheet2. I want to automatically fill up the Sheet2 with the records copied from the Sheet1 with selected columns. For example, Sheet1 looks like following: A B C D R1 1 3 1 1 R2 2 2 2 3 R3 1 1 2 1 R4 4 3 5 4 Now blank Sheet2(to be automatically populated) looks like following: A B R1 R2 R3 R4 What I want is that Records from the Sheet1 should be selected based on criteria and Sheet2 to be filled automatically with selected columns. Example: Records should be searched in Sheet1 where if a row is having column B's or column C's value as "2", then Sheet1's respective row's columns to be populated in Sheet2's column(let us suppose only cloumn A and B) for every qualifying record, And I should get Row2 and Row3(which matches this condition) as records in Sheet2 populated. How can I get this, or which formula approach I can opt? Thank You, Saurabh Khanna. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatic populate Sheet2 with data from Sheet1 based oncriteria.
Why can't you just apply autofilter to Sheet1 for the criteria you are
interested in? (eg Column C, value 2). Then you can copy just the visible data to Sheet2. Hope this helps. Pete On Dec 30, 11:27*am, Saurabh Khanna. <Saurabh wrote: Hello, I have two Worksheets Sheet1 and Sheet2. I want to automatically fill up the Sheet2 with the records copied from the Sheet1 with selected columns. For example, Sheet1 looks like following: * * A B C D R1 1 3 1 1 R2 2 2 2 3 R3 1 1 2 1 R4 4 3 5 4 Now blank Sheet2(to be automatically populated) looks like following: * * *A B R1 R2 R3 R4 What I want is that Records from the Sheet1 should be selected based on criteria and Sheet2 to be filled automatically with selected columns. Example: Records should be searched in Sheet1 where if a row is having column B's or column C's value as "2", then Sheet1's respective row's columns to be populated in Sheet2's column(let us suppose only cloumn A and B) for every qualifying record, And I should get Row2 and Row3(which matches this condition) as records in Sheet2 populated. How can I get this, or which formula approach I can opt? Thank You, Saurabh Khanna. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatic populate Sheet2 with data from Sheet1 based on criteria.
One formulas play which will deliver it here
Assume source data in Sheet1's cols A to D, data from row2 down In Sheet2, Assume the criteria values for the source column (letter) and its chosen numeric value will be entered in A1:A2, eg In A1: B In A2: 2 (ie column B, lines with numeric value 2) Put in C2: =IF(INDIRECT("'Sheet1'!"&$A$1&ROWS($1:1)+1)=$A$2,R OW(),"") Leave C1 blank Put in D2: =IF(ROWS($1:1)COUNT($C:$C),"",INDEX(Sheet1!A:A,SM ALL($C:$C,ROWS($1:1)))) Copy D2 across to G2. Select C2:G2, fill down to cover the max expected extent of source data in Sheet1. Minimize col C. Cols D to G will return the required results from Sheet1 for the criteria inputs in A1:A2, with all lines neatly packed at the top. -- Max Singapore http://savefile.com/projects/236895 Downloads:21,000 Files:370 Subscribers:66 xdemechanik --- "Saurabh Khanna." wrote: I have two Worksheets Sheet1 and Sheet2. I want to automatically fill up the Sheet2 with the records copied from the Sheet1 with selected columns. For example, Sheet1 looks like following: A B C D R1 1 3 1 1 R2 2 2 2 3 R3 1 1 2 1 R4 4 3 5 4 Now blank Sheet2(to be automatically populated) looks like following: A B R1 R2 R3 R4 What I want is that Records from the Sheet1 should be selected based on criteria and Sheet2 to be filled automatically with selected columns. Example: Records should be searched in Sheet1 where if a row is having column B's or column C's value as "2", then Sheet1's respective row's columns to be populated in Sheet2's column(let us suppose only cloumn A and B) for every qualifying record, And I should get Row2 and Row3(which matches this condition) as records in Sheet2 populated. How can I get this, or which formula approach I can opt? Thank You, Saurabh Khanna. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
+/- in sheet2 based on conditions and dynamic rows in sheet1 | Excel Discussion (Misc queries) | |||
How to find a data in Sheet1,automatic transfer to Sheet2(Excel) | Excel Discussion (Misc queries) | |||
Function on Sheet1 based on data on Sheet2 | Excel Worksheet Functions | |||
copying data from sheet1 to sheet2 | Excel Worksheet Functions | |||
How To Retrieve Data from Sheet2 into Sheet1 | Excel Worksheet Functions |