Hi Excel Programmers: Described here is the functionality that staff at a small school would like added to an Excel app for tracking student grades and progress. Any coding help would be greatly appreciated!!
Here are the workbook’s parts: There is a sheet named “Students” in which last and first names of students are imported into cols A and B. On this sheet are also two Microsoft Date Time Picker controls for selecting starting and ending report dates. There are 7 worksheets named Period 1, Period 2,…, Period 7 (for 7 different school classes). A student may be entered in just one of the periods, may be in 3 periods, all 7 periods, whatever. If they're in multiple periods, students are Not necessarily entered on the same rows. A worksheet named “Progress Report Template” is in the workbook to be used for reports. Here is the basic setup of the 7 worksheets named Period 1, Period 2, …, Period 7: Last and first names of students are in columns B and C on every ODD row, starting on row 13. Dates are inserted in row 12; each day’s date is in a separate column. There are other headings along this row, not just dates, but the date columns are entered consecutively. Comments about a student’s work that period are typed in the date columns, but 1 row Below which the student’s names have been inserted (so the comments are on every EVEN row). Here is the desired functionality: Teachers would like to go to the sheet named “Students,” select starting and ending dates from the Microsoft Date Time Picker controls (E3 and E5), click a blank cell next to a student’s name (to select that student to report on), say, a cell in col D, and then have Excel do the following: 1. Store the last and first names of the student from cols A and B as variables. 2. Store the starting and ending dates from E3 and E5. 3. Create a copy of the worksheet named “Progress Report Template” in the workbook. 4. Rename the copy of “Progress Report Template” with the student’s first and last names. 5. In the student’s new report worksheet, input his/her last name in cell B1 and his/her first name in B2. 6. In the student’s new report worksheet, insert the starting date in B6 (or a row below any existing data). 7. Here’s the crazy part – scan Period 1 for the student’s last and first names (would be in cols B and C on the "Period" sheets). If found, do the following: 8. Copy the typed comment (if any) in the cell at the intersection of 1 row below the student’s names (cols B and C) and the column with the date heading in row 12 that matches the starting date. 9. Return to the student’s report worksheet and insert the starting date in cell B6 and the corresponding comment in cell C6 (or a row below existing data). 10. Then, go to and scan Period 2 and do the same if the student is present—copy the comment at the intersection of 1 row below the student’s names and the column with the matching date in row 12, return to the student’s report worksheet and insert the date and comment [a row below the previously used row]. 11. Repeat this for each Period’s sheet. 12. AND—for a range of dates (from the Microsoft Date and Time Pickers in the Student’s sheet)—repeat this for the next consecutive date— scanning the Period worksheets, copying the comments for the students on those days, then copying and listing them on the student’s report sheet. 13. Lastly, set the newly inserted dates and corresponding comments as the new print range (everything above row 5 will be the page’s heading). Don’t want very much do we? Does this make sense? I realize this is very complex. Again, any help on this would be super appreciated. Thanks. -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
