Here's the implemented template:
http://freefilehosting.net/download/404am
Quote Log to MthYr Production Template.xls

In your source ("parent") sheet: Quote Log,
data is in row3 down, where
key cols are col F ("Yes" indications under "Accepted" col),
and col J (the est delivery dates - these are real dates)

List the MthYr as text* in Q2 across, eg: Jan08, Feb08, etc
*Either enter a leading apostrophe eg: 'Jan08, or preformat Q2 across as TEXT
Leave Q1:IV1 empty

In Q3:
=IF($F3<"yes","",IF(TEXT($J3,"mmmyy")=Q$2,ROW()," "))
Copy Q3 across/fill down to cover max expected extent of source data in cols
F & J

Extra: You have a "Total Hours" col in col I, which figs are to be extracted
from the respective mthyr sheets for the particular quote#

Place in I3, copy down:
=IF(J3="","",INDEX(INDIRECT("'"&TEXT(J3,"mmmyy")&" '!I:I"),MATCH(B3,INDIRECT("'"&TEXT(J3,"mmmyy")&"'! B:B"),0)))

Create this defined name WSN
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 your 1st "child" sheet (ie your template sheet for the monthly
production),
rename it as the 1st MthYr, say: Jan08
Place in B3:
=IF(ROWS($1:1)COUNT(OFFSET('Quote Log'!$P:$P,,MATCH(WSN,'Quote
Log'!$Q$2:$IV$2,0))),"",
INDEX('Quote Log'!B:B,MATCH(SMALL(OFFSET('Quote Log'!$P:$P,,MATCH(WSN,'Quote
Log'!$Q$2:$IV$2,0)),ROWS($1:1)),OFFSET('Quote Log'!$P:$P,,MATCH(WSN,'Quote
Log'!$Q$2:$IV$2,0)),0)))
Copy B3 across to H3, fill down to cover max expected number of lines per
any delivery MthYr, say down to row50?

You have your own formula in I3 down: =SUM(K3:P3)
to compute "Total Hours" from other inputs in adjacent cols to the right

Place in J3:
=IF(ROWS($1:1)COUNT(OFFSET('Quote Log'!$P:$P,,MATCH(WSN,'Quote
Log'!$Q$2:$IV$2,0))),"",
INDEX('Quote Log'!J:J,MATCH(SMALL(OFFSET('Quote Log'!$P:$P,,MATCH(WSN,'Quote
Log'!$Q$2:$IV$2,0)),ROWS($1:1)),OFFSET('Quote Log'!$P:$P,,MATCH(WSN,'Quote
Log'!$Q$2:$IV$2,0)),0)))
Copy J3 down similarly as for B3:H3

Extra: To create a dynamic title for the child sheet in the top row which
takes in the sheetname automatically,
Place in A1: ="Monthly Production: "&WSN

The source lines for Jan08 which satisfy the criteria will be autocopied
from sheet: Quote Log and appear neatly bunched at the top. Dress this child
sheet up to taste. Then just make copies of it & rename these as the other
MthYr, viz: Feb08, Mar08, etc.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,100 Files:360 Subscribers:56
xdemechanik
---