Here's a formulas driven model which delivers it ...
Illustrated in this sample:
http://freefilehosting.net/download/4021h
AutoCopy to Month Sheet.xls
In the source ("parent") sheet: x,
data is assumed in row2 down, where
key cols are col D ("Yes"),
and col E (est delivery dates - these are real dates)
List the MthYr as text* in G1 across, eg: Sep08, Oct08, etc.
*Either enter a leading apostrophe eg: 'Sep08, or preformat G1 across as TEXT
In G2: =IF($D2<"Yes","",IF(TEXT($E2,"mmmyy")=G$1,ROW()," "))
Copy G2 across/fill down to cover max expected extent of source data in cols
D & E
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
formulas. It will auto-extract the sheetname implicitly. Technique came from
a post by Harlan.
Then in the 1st "child" sheet (a new sheet),
name it as the MthYr, say: Sep08
Paste the col headers from x into A1:E1
Place in A2:
=IF(ROWS($1:1)COUNT(OFFSET(x!$F:$F,,MATCH(TRUE,IN DEX(TEXT(x!$G$1:$K$1,"mmmyy")=WSN,),))),"",INDEX(x !A:A,MATCH(SMALL(OFFSET(x!$F:$F,,MATCH(TRUE,INDEX( TEXT(x!$G$1:$K$1,"mmmyy")=WSN,),)),ROWS($1:1)),OFF SET(x!$F:$F,,MATCH(TRUE,INDEX(TEXT(x!$G$1:$K$1,"mm myy")=WSN,),)),0)))
Copy A2 across to E2, fill down to cover max expected number of lines per
any delivery MthYr, say down to row50? The source lines for Sep08 which
satisfy the criteria will be autocopied from x and appear neatly bunched at
the top. Dress this child sheet up to taste. Then just make copies of this
sheet: Sep08, & rename these as the other MthYr, viz: Oct08, Nov08, etc.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,100 Files:360 Subscribers:56
xdemechanik
---
"Kingdazza" wrote:
I use excel as a production program for our manufacturing company but
currently move everything over manually. When a job is quoted it gets
inputted in as follows; Date/quote #/Name/Accepted/Delivery Date. When the
job is accepted we type 'YES' under accpeted and put in an estimated delivery
date. When this is done I want that row to move to the month of the delivery
date in another sheet. Can anyone help