Long-term data collection and filtering

[Alexjj]

So, I am a little familiar with excel and that is greatly in part to all of you who continuously help us out on these forums...thank you so much for all of you who provide guidance, codes, and troubleshooting.

Here's what I've been asked to do at my elementary school.
1. I need to device a data collection spread sheet for each teacher at my school...that's 30 teachers. I've got that done. I created 1 workbook for each grade level K-5 where each teacher has their own sheet...for example, 5th grade has a workbook called 5th Grade Reading Data...each teacher has their own sheet (Bob, Mary, Sue, and John).

The data sheet has multiple columns labeled Student Names, Book Title, Reading Level, Kinder, 1st, 2nd, 3rd, 4th, 5th.

2. The idea is that a teacher at Kinder will use the data sheet to collect information about her students and then at the end of the year, she will select a 1st grade teacher's name from a drop down box in the 1st grade column for each of her students and that data would go to a new sheet for the assigned 1st grade teacher. Let's say that the student assigned to the 1st grade teacher is named Julie.

When Julie's data is sent to the new 1st grade teacher, the 1st grade teacher would then add more data to that student's data sheet and the process would continue from year to year so that when Julie is in 5th grade, her 5th grade teacher would be able to see her records since Kinder.

Any ideas on how I could make this happen? I had thought about using Paste Links to collect the data from every grade level into a centrally located workbook and then having that workbook copy data to the next year's teacher on a new workbook...This is driving me crazy...any suggestions, ideas, or "I feel your pain" comments are welcome...maybe I'm going about this the wrong way...I'm stuck, any advice is greatly appreciated!!!

Thank you in advance!
Alex

 

[Dartagnan]

You may want to consider using Access. You could build tables to create student to teacher relationships and store notes over the years.

 

[Akashwani]

Hi Alex,

How about emailing the Student sheet to each teacher?

http://www.rondebruin.nl/sendmail.htm

I guess you could set up the original Workbook with each Student having their own Worksheet, you could use your drop down list and have the Worksheet emailed to the next teacher, who would then add it to their Workbook and the process carries on.

I don't write or understand vba so I couldn't help you with that, sorry.
Once you have set up your workbook and Worksheets to how you want, read the link that I have posted and see if any of the options are of use, if you require additional vba code, then you know where to post

Good luck, sounds an interesting project.

Ak

 

[Alexjj]

<style> <!-- /* Font Definitions */ @font-face {font-family:"ＭＳ 明朝"; panose-1:0 0 0 0 0 0 0 0 0 0; mso-font-charset:128; mso-generic-font-family:roman; mso-font-formatther; mso-font-pitch:fixed; mso-font-signature:1 134676480 16 0 131072 0;} @font-face {font-family:"ＭＳ 明朝"; panose-1:0 0 0 0 0 0 0 0 0 0; mso-font-charset:128; mso-generic-font-family:roman; mso-font-formatther; mso-font-pitch:fixed; mso-font-signature:1 134676480 16 0 131072 0;} @font-face {font-family:Cambria; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:0; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:3 0 0 0 1 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:Cambria; mso-ascii-font-family:Cambria; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:"ＭＳ 明朝"; mso-fareast-theme-font:minor-fareast; mso-hansi-font-family:Cambria; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; font-family:Cambria; mso-ascii-font-family:Cambria; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:"ＭＳ 明朝"; mso-fareast-theme-font:minor-fareast; mso-hansi-font-family:Cambria; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} @page WordSection1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.WordSection1 {page:WordSection1;} --> </style> [FONT=&quot]Hmmm, looks like I may need to alter my plans. I really don't want to start having to learn Access when I've just started getting somewhat of hang on Excel. Ok, so how about this:

1. One workbook per grade level with 6 sheets. 1 sheet per teacher and 1 sheet as a master list for the whole grade level.[/FONT]
[FONT=&quot]
2. As teachers fill out the data on their sheets, their data gets copied onto the master sheet. The Master will be located in the same workbook, so I was planning on simply doing a Paste Link from each of the individual teacher’s sheets onto their respective cells. What do you think about Paste Link?[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]3. Here is where it gets tricky. I need a separate workbook with tables that copies the Data from ALL the Masters from grades K – 5 so that we can filter the data and analyze it to our heart’s content. [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]4. On the School Master List, there will be a final column labeled “Next Year’s Teacher” which will be a pull down that lists all the teachers in our school (30 teachers). At the end of the school year, we place students into their next classroom. So if Teacher A places a student named Suzy into Teacher B’s classroom by selecting his name from the pull down menu and another student named John is also placed into Teacher B’s classroom, I need to have the row that contains each of Suzy’s and John’s data to be copied into a workbook for Teacher B’s grade level and his specific sheet so that he can have immediate access to all his new students’ data without having to deal with the school wide master list.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Any ideas? Can I just use Paste Links or is there a better, more effective, or otherwise more useful way to do this; keeping in mind that not all the workbooks will be open at the same time.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]I’m attaching the basic data collection spreadsheet so that you can see what the teachers will be using in steps 1 and 2. It’s steps 3 and 4 that I’m unsure about.[/FONT]
[FONT=&quot] [/FONT]
Thanks again everyone…I really do appreciate your help. I know that there are programs out there that do exactly this type of stuff, but our elementary school simply cannot afford those programs and so we’re left to use whatever resources are available to us and Excel is it…we don’t even own Access. Oh, I do all this on a Mac OS X Excel 2011.


Hmmm, how do I attach my document for you to look at?


Thanks!!
[FONT=&quot]Alex
[/FONT]

 

[Akashwani]

Hi Alex,

You need to upload your file to a site like 4shared or boxnet.

Then post your link here.

Take a look here and see if it gives you any ideas.
It' a sample workbook, not a solution.


Alexjj2.xls


Good luck.

Ak

 

[Alexjj]

OK..thanks for the information setting up and account with 4Shared. Here is the link to the mock sheets. It's one workbook with 5 sample sheets. First 2 sheets are labeled Teacher 1 and Teacher 2. The 3rd sheet is labeled 1st Grade Master and this sheet would in the same workbook as the Teachers' sheets. The 4th sheet is labeled School Master and this one would be in a different workbook, so as to centralize the file. The last sheet is labeled Mr. Jones, who would act as the "next year's" teacher file.

Again, each teacher enters their individual data and that data is copied onto the Grade Level Master, from the Grade Level Master that data is then copied to the School Wide Master. Can I just use Paste Link up to this point even though some of the workbooks might be closed or is there a better way to propagate the cells in subsequent lists?

On the School Wide sheet, you'll see one additional column at the end "next year's teacher", which is a drop down listing all the teachers at the school. What also need to know is, how can I can get the row that contains a student's data copied into Mr. Jones' data sheet if his name is selected from the drop down menu and for every other student that is assigned him as a teacher? Is there a "If Mr. Jones is selected in this column then copy this entire row to Mr. Jone's data sheet?" function

Thanks again for all everyone is doing to help me and my fellow teachers. This type of work will enable us to focus more on addressing our student's needs rather than running around trying to find out their previous scores and areas of need. If I, you, we, can figure how to do this, I'm confident I can do this for math and writing as well.
[FONT=&quot]
[/FONT]
http://www.4shared.com/document/L89jOkzH/DRA2_Reporting-ver9.html

 

[Akashwani]

Hi Alex,

This may help you solve the linking issue.

http://www.youtube.com/watch?v=-foPogO3QNE

Ak

 

[Alexjj]

As always, I appreciate your help...I aspire to have the depth of knowledge that you and others like yourself possess.

Have a wonderful weekend!

Alex