dates linked to data
 

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

dates linked to data
 

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 ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Chersie" wrote:
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!!!

dates linked to data
 

Max, this was perfect!!!! I can't thank you enough! I loved the example
project. It helped me immensely in setting up my workbook!

I do have another question for you. I have a spreadsheet in which I want to
write a macro to take a list of names from worksheet1 in column A and have
each consecutive worksheet given the subsequent names in the list A1, A2, A3,
etc.

If you can help me with that, I then have one more question. One at a time
though ;)

Thank you!
Chersie



"Max" wrote:

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 ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Chersie" wrote:
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!!!

dates linked to data
 

"Chersie" wrote:
Max, this was perfect!!!! I can't thank you enough! I loved the example
project. It helped me immensely in setting up my workbook!

Welcome, delighted to hear that.

I do have another question for you. ..

I'm out, sorry. Suggest you try a fresh posting in .programming
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---