One formulas play which delivers it automatically into respective MthYr
sheets, with lines auto-sorted in chrono order as well ..
Illustrated in this sample:
http://www.freefilehosting.net/download/3f3bc
AutoCopy Data By MthYr To Resp Sheet.xls
In sheet: x (the "master")
Assume data in cols A to C, from row2 down,
with the key col = col A (Dates, containing real dates)
List as *text* in K1 across: Jan08, Feb08, Mar08, etc (in any order)
Put in K2:
=IF($A2="","",IF(TEXT($A2,"mmmyy")=K$1,$A2+ROW()/10^10,""))
Copy K2 across as far as required, then fill down to cover the max expected
extent of source data
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.
In a new sheet named: Jan08
With the same col headers pasted into A1:C1
Put in A2:
=IF(ISERROR(SMALL(OFFSET(x!$J:$J,,MATCH(WSN,x!$K$1:$IV$1,0)),ROWS($1:1))),"",INDEX(x!A:A,MATCH(SMALL(OFFSET(x!$J:$J,,MATCH(WSN,x!$K$1:$IV$1,0)),ROWS($1:1)),OFFSET(x!$J:$J,,MATCH(WSN,x!$K$1:$IV$1,0)),0)))
Copy A2 across to C2, fill down to say, C25 (copy down by the smallest
possible range sufficient to cover the max expected extent for any single
mth/yr). Format col A as date.
Cols A to C will return only the lines for the mth/yr : Jan08 from x,
with all lines neatly bunched at the top, arranged in chronological order by
date. Any lines with identical/duplicate dates will be returned as well, in
relative order.
Then just make a copy of the sheet: Jan08, rename it as: Feb08, and you'd
get the results for Feb08. Repeat the copy > rename sheet process to get the
rest of the 12 mth/yr sheets (a simple one-time job). Adapt to suit ..
---
Chersie said:
I have worksheet one set up as a form to gather information. I have twelve
consecutive worksheets for the months of the year.
I would like to link the data from sheet1 to the appropriate month the data
is for. I am not sure how to do this.
I want all input data from January 1st to January 31st from the form to go
to the worksheet January, February 1st to February 29th to go to worksheet
February, etc.
How can I do this?
Thanks in advance for your help!!!