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

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

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

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

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

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!