Transfer certain data from Sheet 1 to sheet 2

L

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

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
 
L

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
 
M

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top