Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Transfer certain data from Sheet 1 to sheet 2
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Transfer certain data from Sheet 1 to sheet 2
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 On Feb 2, 9:01*am, "Leah Davis" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Transfer certain data from Sheet 1 to sheet 2
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" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Transfer certain data from Sheet 1 to sheet 2
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. "Pete_UK" wrote in message ... 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 On Feb 2, 9:01 am, "Leah Davis" wrote: 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Transfer certain data from Sheet 1 to sheet 2
Well, you could use Advanced Filters - Debra Dalgleish has an intro
he http://www.contextures.com/xladvfilter01.html Hope this helps. Pete On Feb 2, 10:11*am, "Leah Davis" wrote: 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."Pete_UK" wrote in message ... 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 On Feb 2, 9:01 am, "Leah Davis" wrote: 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.- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Transfer certain data from Sheet 1 to sheet 2
.. 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,Shee t1!A2<=A$3),ROW(),"")) Leave C1 empty In D2: =IF(ROWS($1:1)COUNT($C:$C),"",INDEX(Sheet1!A:A,SM ALL($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 --- |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Transfer certain data from Sheet 1 to sheet 2
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,Shee t1!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: 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,Shee t1!A2<=A$3, Sheet1!D2="A*"),ROW(),"")) but this doesn't work. Would appreciate if you could help with this. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Transfer cell data from sheet to sheet? | Excel Discussion (Misc queries) | |||
How can I transfer a required data from sheet 1 to sheet 2 automat | Excel Discussion (Misc queries) | |||
how to transfer data from sheet to sheet with new range? | Excel Discussion (Misc queries) | |||
How to Automatically transfer specific Data from Sheet 1 to Sheet | Excel Worksheet Functions | |||
Transfer data from sheet to sheet | Excel Discussion (Misc queries) |