Transfer certain data from Sheet 1 to sheet 2

[Leah Davis]

Hi,

I would like to put a function into my spreadsheet that looks up data
meeting a certain criteria in sheet 1 then transfers the results into sheet
2. For example Sheet 1 has 5 columns of data - one of these columns contains
dates. In sheet 2 I would like to have all the data from Sheet 1 that falls
within a date range that I specify.

Any help appreciated.

Thanks.

 

[Pete_UK]

You could apply autofilter on the date column in Sheet1 (choose Custom
to set your range), and then copy the visible data across to Sheet2.

Hope this helps.

Pete

 

[Ashish Mathur]

Hi,

You can also work with Advanced filters. They are very well explained in
Excel's Help menu.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Leah Davis]

Hi Pete,

That's what I have been doing. I was wondering if there was a formula I
could insert in sheet 2 that would do the work for me.

Thanks.
You could apply autofilter on the date column in Sheet1 (choose Custom
to set your range), and then copy the visible data across to Sheet2.

Hope this helps.

Pete

 

[Pete_UK]

Well, you could use Advanced Filters - Debra Dalgleish has an intro
here:

http://www.contextures.com/xladvfilter01.html

Hope this helps.

Pete

 

[Max]

.. wondering if there was a formula I could insert in sheet 2 that would

do the work

Assume source table in Sheet1's cols A to C,
data from row2 down, where col A is the key dates col (real dates)

In Sheet2,
Assume Inputs for StartDate & EndDate will be done in A2:A3
In C2:
=IF(COUNT(A$2:A$3)<2,"",IF(AND(Sheet1!A2>=A$2,Sheet1!A2<=A$3),ROW(),""))
Leave C1 empty

In D2:
=IF(ROWS($1:1)>COUNT($C:$C),"",INDEX(Sheet1!A:A,SMALL($C:$C,ROWS($1:1))))
Copy D2 to F2. Select C2:F2, copy down to cover the max expected extent of
data in Sheet1, say down to row 200? Minimize col C. Format col D as date.
Cols D to F will return the required results from Sheet1, all neatly packed
at the top.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---

 

[Max]

Response to OP's additional criteria request:

---------------
Leah,

Pl follow up in the newsgroup thread, not via private email.

You could enhance the criteria this way, in C2, copied down:
=IF(COUNT(A$2:A$3)<2,"",IF(AND(Sheet1!A2>=A$2,Sheet1!A2<=A$3,OR(Sheet1!D2={"AA","AT"})),ROW(),""))

Max
----- Original Message ----
From: Leah Davis
To: Max
Sent: Monday, February 9, 2009 7:01:04 PM
Subject: Re: Transfer certain data from Sheet 1 to sheet 2

Hi Max,

Thanks very much for this. It is the formula I have been looking for. I have
one addition I need to make to finish off the spreadsheet. Column D on
sheet1 contains a list which offers the following choice of data:

AA
AT
IX
CD
IA

The data I need copied to sheet 2 has the date criteria you have already
plus must only include rows which have either AA or AT in column D. I am not
quite sure how to modify the formula you provided to do this.

I thought it could be
=IF(COUNT(A$2:A$3)<2,"",IF(AND(Sheet1!A2>=A$2,Sheet1!A2<=A$3,
Sheet1!D2="A*"),ROW(),"")) but this doesn't work. Would appreciate if you
could help with this.

Thanks.