Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copy data from one worksheet to another
Is there a function or command I can use to get excel to automatically copy a
row from the master worksheet over to a seperate monthly sheet when the data in a certain cell matches a range of criteria i.e. Master (Wksht 1) A B C Joe Smith Active 11/01/07 John Doe Closed 11/10/07 Justine A Pending 11/08/07 Kim Al Active 12/07/07 Monthly (Wksht 2) A B C =the entire row if cell C is within 11/01/07 - 11/30/07 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copy data from one worksheet to another
Here's one way to get there ..
Assume source data in sheet: Master, cols A to C, data from row1 down where col C houses the dates (real dates are presumed) Click Insert Name Define Put under "Names in workbook:": WSN Put in the "Refers to:" box: =MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")))+1,32) Click OK The above defines WSN as a name we can use to refer to the sheetname in any sheet. It will auto-extract the sheetname implicitly. Technique is Harlan Grove inspired. Then in a sheet named: Nov07 Put in A1: =IF(Master!C1="","",IF(TEXT(Master!C1,"mmmyy")=WSN ,ROW(),"")) Put in B1: =IF(ROW()COUNT($A:$A),"",INDEX(Sheet1!A:A,SMALL($ A:$A,ROW()))) Copy B1 across to D1. Select A1:D1, copy down to the max expected extent of data in Sheet1. Hide away col A. Format col D as dates to taste. Cols B to D will return the results sought, ie lines for Nov07, with all results neatly bunched at the top. To propagate for other month/yr Eg: To extract lines for Dec07, just make a copy of Nov07 and rename the sheet as: Dec07, and you'd get the lines for Dec07. And so on, extend as desired for each month/yr, easily. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "LL" wrote: Is there a function or command I can use to get excel to automatically copy a row from the master worksheet over to a seperate monthly sheet when the data in a certain cell matches a range of criteria i.e. Master (Wksht 1) A B C Joe Smith Active 11/01/07 John Doe Closed 11/10/07 Justine A Pending 11/08/07 Kim Al Active 12/07/07 Monthly (Wksht 2) A B C =the entire row if cell C is within 11/01/07 - 11/30/07 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copy data from one worksheet to another
Typo: Line
.. Select A1:D1, copy down to the max expected extent of data in Sheet1. should read as: .. Select A1:D1, copy down to the max expected extent of data in Master. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copy data from one worksheet to another
Apologies, another correction
Lines Put in B1: =IF(ROW()COUNT($A:$A),"",INDEX(Sheet1!A:A,SMALL($ A:$A,ROW()))) should read Put in B1: =IF(ROW()COUNT($A:$A),"",INDEX(Master!A:A,SMALL($ A:$A,ROW()))) (I changed the source sheetname from "Sheet1" to "Master" halfway through in testing here) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copy data from one worksheet to another
Thanks for the assistance. I will tweak this to the names on the actual
sheet and make it work... Such a time saver!! THANKS! "Max" wrote: Apologies, another correction Lines Put in B1: =IF(ROW()COUNT($A:$A),"",INDEX(Sheet1!A:A,SMALL($ A:$A,ROW()))) should read Put in B1: =IF(ROW()COUNT($A:$A),"",INDEX(Master!A:A,SMALL($ A:$A,ROW()))) (I changed the source sheetname from "Sheet1" to "Master" halfway through in testing here) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copy data from one worksheet to another
Welcome, LL.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "LL" wrote in message ... Thanks for the assistance. I will tweak this to the names on the actual sheet and make it work... Such a time saver!! THANKS! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy from B worksheet to A worksheet with NO repeated data | Excel Discussion (Misc queries) | |||
copy data from a worksheet | Excel Worksheet Functions | |||
copy data in a cell from worksheet A to worksheet B | Excel Discussion (Misc queries) | |||
Copy Data from one worksheet to another | Excel Discussion (Misc queries) | |||
Copy Data to Another Worksheet | Excel Worksheet Functions |