If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Auto copy data rows between sheets depending on date entry
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. |
#2
|
|||
|
|||
Auto copy data rows between sheets depending on date entry
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 --- "Struggling in Sheffield" wrote: 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. |
#3
|
|||
|
|||
Auto copy data rows between sheets depending on date entry
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 --- |
#4
|
|||
|
|||
Auto copy data rows between sheets depending on date entry
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. "Max" wrote: 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 --- "Struggling in Sheffield" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|