Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,856
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
+/- in sheet2 based on conditions and dynamic rows in sheet1 deepika :excel help[_2_] Excel Discussion (Misc queries) 0 February 4th 08 09:33 AM
How to find a data in Sheet1,automatic transfer to Sheet2(Excel) Edmond Excel Discussion (Misc queries) 1 March 6th 07 05:38 AM
Function on Sheet1 based on data on Sheet2 Sony Excel Worksheet Functions 0 February 7th 07 05:44 PM
copying data from sheet1 to sheet2 Rookie Excel Worksheet Functions 3 September 7th 06 12:09 PM
How To Retrieve Data from Sheet2 into Sheet1 compconnj Excel Worksheet Functions 3 March 21st 06 08:56 PM


All times are GMT +1. The time now is 08:58 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"