Auto copy data rows between sheets depending on date entry

[Struggling in Sheffield]

Hi,
I’m having problems finding a way to auto copy rows of data from one
worksheet to another depending on a date entry within the data row. I’ve
created the following simple example to illustrate what I’m trying to achieve.

I have a workbook containing 13 worksheet tabs.
First worksheet is named ‘Raw data’, then 12 worksheets follow named
‘January’ to ‘December’.
I enter rows of data into the first worksheet like the example below:

‘Raw data’ worksheet

A B C D E
1 Nº Date Forename Surname Address
2 25 Mon 6 Jan 09 Sid James London
3 46 Sat 5 Feb 09 Frank Spencer Reading
4 53 Tue 8 Feb 09 Paul Cooper Plymouth
5 55 Fri 26 Feb 09 Phil Walker Leeds
6 72 Wed 23 May 09 John Davis Hull
7 75 Thu 5 Jul 09 Ed Grant Derby
8 81 etc
9 92


‘January’ to ‘December’ are set up to hold the same data as that entered in
the ‘Raw data’ worksheet.
On calculate I’d like the ‘January’ to ‘December’ worksheets to look up the
column of dates in the ‘Raw data’ sheet so that, for example, the ‘February’
sheet would copy all the rows of data that contain Feb in the Date column
into itself, in the same order (top to bottom) in which the data has been
originally entered into the ‘Raw data’ sheet:

‘February’ worksheet

A B C D E
1 Nº Date Forename Surname Address
2 46 Sat 5 Feb 09 Frank Spencer Reading
3 53 Tue 8 Feb 09 Paul Cooper Plymouth
4 55 Fri 26 Feb 09 Phil Walker Leeds
5
6
7
8
9


I’ve managed to do something vaguely similar looking up lists within the
same worksheet but am just struggling with looking into another sheet.
Hope my example is clear enough to follow. Many thanks for looking and hope
you can help.
Cheers.

 

[Max]

Here's a relatively simple formulas model option which delivers the exact
automated functionalities you seek. You can get it up and running in a matter
of minutes ...

Source data in sheet: Raw data, cols A to E, data from row2 down, as posted
The "Dates" in B2 down are assumed real dates recognized by Excel, albeit
col B could have been custom formatted as: ddd dd-mmm-yy to appear in the
date format that you depict in your post. Think your data in col B needs to
be checked/corrected to be real dates as for eg: 6 Jan 09 is a Tues, not Mon.
Do this first otherwise you won't see the model functioning as advertised.

In Raw data,
a. Create a criteria range pointing to col B to flag lines by the month
List as TEXT in G1:R1, the 12 "child" sheetnames: January, February, etc
In G2: =IF(TEXT($B2,"mmmm")=G$1,ROW(),"")
Copy across to R2, fill down to cover the max expected extent of source
data, say down to R500?

b. Create a defined name which can return the sheetname in any sheet in the
book (Harlan's technique) Pre-requisite: Your book must be saved beforehand
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.

Then in a new sheet, name it as say: January
Paste the same col labels into A1:E1
Put in A2:
=IF(ROWS($1:1)>COUNT(OFFSET('Raw data'!$F:$F,,MATCH(WSN,'Raw
data'!$G$1:$R$1,0))),"",INDEX('Raw data'!A:A,SMALL(OFFSET('Raw
data'!$F:$F,,MATCH(WSN,'Raw data'!$G$1:$R$1,0)),ROWS($1:1))))
Copy across to E2, fill down to cover the max expected number of lines for
any particular month, say down to E30? You should see only the source lines
for "January" populate within the formulated range, neatly packed at the top,
in the same relative order that these lines appear within the source data.
Exactly what you seek. Format col B as dates to taste. Dress up all else on
the sheet as desired. Then just copy this sheet and rename it as the next
month: February, and you'd get all the lines for February. Repeat the
copy/rename another 10x to create for the rest of the months. That's it.
Success? Celebrate it, hit the YES below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---

 

[Max]

Refinement, line:

In G2: =IF(TEXT($B2,"mmmm")=G$1,ROW(),"")

should read as:
In G2: =IF($B2="","",IF(TEXT($B2,"mmmm")=G$1,ROW(),""))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---

 

[Struggling in Sheffield]

Max,
Thanks very much for that, it works brilliant and is light years in front of
what I could have achieved at the moment.
Don't worry about the dates in my simplified example - they were just made
up to illustrate what I was trying to do. It took a while to successfully
transpose your solution to my more complicated real life problem but I got
there after a while.
Only trouble now is because I'm auto-populating the data I'm having to
re-format my sheets to get rid of all the annoying ISERRORs.
Solving one problem always seems to just help you arrive at the next one!
Thanks again Max.
Steve.

Here's a relatively simple formulas model option which delivers the exact
automated functionalities you seek. You can get it up and running in a matter
of minutes ...

Source data in sheet: Raw data, cols A to E, data from row2 down, as posted
The "Dates" in B2 down are assumed real dates recognized by Excel, albeit
col B could have been custom formatted as: ddd dd-mmm-yy to appear in the
date format that you depict in your post. Think your data in col B needs to
be checked/corrected to be real dates as for eg: 6 Jan 09 is a Tues, not Mon.
Do this first otherwise you won't see the model functioning as advertised.

In Raw data,
a. Create a criteria range pointing to col B to flag lines by the month
List as TEXT in G1:R1, the 12 "child" sheetnames: January, February, etc
In G2: =IF(TEXT($B2,"mmmm")=G$1,ROW(),"")
Copy across to R2, fill down to cover the max expected extent of source
data, say down to R500?

b. Create a defined name which can return the sheetname in any sheet in the
book (Harlan's technique) Pre-requisite: Your book must be saved beforehand
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.

Then in a new sheet, name it as say: January
Paste the same col labels into A1:E1
Put in A2:
=IF(ROWS($1:1)>COUNT(OFFSET('Raw data'!$F:$F,,MATCH(WSN,'Raw
data'!$G$1:$R$1,0))),"",INDEX('Raw data'!A:A,SMALL(OFFSET('Raw
data'!$F:$F,,MATCH(WSN,'Raw data'!$G$1:$R$1,0)),ROWS($1:1))))
Copy across to E2, fill down to cover the max expected number of lines for
any particular month, say down to E30? You should see only the source lines
for "January" populate within the formulated range, neatly packed at the top,
in the same relative order that these lines appear within the source data.
Exactly what you seek. Format col B as dates to taste. Dress up all else on
the sheet as desired. Then just copy this sheet and rename it as the next
month: February, and you'd get all the lines for February. Repeat the
copy/rename another 10x to create for the rest of the months. That's it.
Success? Celebrate it, hit the YES below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---

I’m having problems finding a way to auto copy rows of data from one
worksheet to another depending on a date entry within the data row. I’ve
created the following simple example to illustrate what I’m trying to achieve.

I have a workbook containing 13 worksheet tabs.
First worksheet is named ‘Raw data’, then 12 worksheets follow named
‘January’ to ‘December’.
I enter rows of data into the first worksheet like the example below:

‘Raw data’ worksheet

A B C D E
1 Nº Date Forename Surname Address
2 25 Mon 6 Jan 09 Sid James London
3 46 Sat 5 Feb 09 Frank Spencer Reading
4 53 Tue 8 Feb 09 Paul Cooper Plymouth
5 55 Fri 26 Feb 09 Phil Walker Leeds
6 72 Wed 23 May 09 John Davis Hull
7 75 Thu 5 Jul 09 Ed Grant Derby
8 81 etc
9 92


‘January’ to ‘December’ are set up to hold the same data as that entered in
the ‘Raw data’ worksheet.
On calculate I’d like the ‘January’ to ‘December’ worksheets to look up the
column of dates in the ‘Raw data’ sheet so that, for example, the ‘February’
sheet would copy all the rows of data that contain Feb in the Date column
into itself, in the same order (top to bottom) in which the data has been
originally entered into the ‘Raw data’ sheet:

‘February’ worksheet

A B C D E
1 Nº Date Forename Surname Address
2 46 Sat 5 Feb 09 Frank Spencer Reading
3 53 Tue 8 Feb 09 Paul Cooper Plymouth
4 55 Fri 26 Feb 09 Phil Walker Leeds
5
6
7
8
9


I’ve managed to do something vaguely similar looking up lists within the
same worksheet but am just struggling with looking into another sheet.
Hope my example is clear enough to follow. Many thanks for looking and hope
you can help.
Cheers.