How to update year groups and organise archive within school tracking & monitoring system

Hi,

Sorry for posting two questions within the one post- my reason for this is that I do not know which I would need to complete first in order to achieve end result. ???

So...
I have created (with a lot of help) a tracking and monitoring module to record information regarding pupil assessments and pastoral notes. This worked fine for our school last year, however now that we are into a new session I have experienced some problems that need resolved.

1. Pupils need moved up a year group.
- By clicking on the link titled 'Add or Remove Pupil Names' I can manage the children within the system. The rest of the pages update from this one. Each child has moved up a year group with new children coming into nursery. I was planning on just going through the list and changing everyone's 'stage' to a year on (so for example P4 would change to P7). I would then remove the leavers (last years P7s) then add the new nursery children.
HOWEVER- Please can anyone spot if this would cause any errors with the system and is there an easier way to do this? Can there be a macro created that I could click on each year that would automatically update each child's year group.

2. This looks to be the bigger issue... By clicking on the link titled 'Summative Assessments' assessment data is displayed for each child. Unlike some of the other data within this system- this sheet will not be updated throughout the year- instead I need to archive this information then create a new sheet for this session. This will allow us to track a child's attainment year on year. I am thinking that when you click on 'Summative Assessments' instead of it displaying the data straight away, it would be better if it took us to an intermediate sheet that displayed five separate links- one for each year for the next five years ('2014-2015', '2015-2016', '2016-2017' etc). By clicking on each of these it would then show the summative assessments for that particular session.
The Summative Assessment sheet reads information from other sheets in the workbook. Therefore, a similar pathway may need created within the links titled 'Raw Data Pips+' and 'Raw Data InCAS+' ??

I would be extremely grateful for any input into this problem. Thank you all so much for your ongoing support!!

Please note: all names have been removed from this workbook for confidentiality reasons.

Kev

Any support with this would be a huge help

Hi there,

This is an IMPRESSIVE application!!!

Regarding:

I was planning on just going through the list and changing everyone's 'stage' to a year on (so for example P4 would change to P7). I would then remove the leavers (last years P7s) then add the new nursery children.

what are the "yearly progression steps"? I would have expected P4 to become P5, or P6 to become P7, but I don't know how the system works.

I would think that it shouldn't be too difficult to provide a button which would do what your first question asks.

Regards,

Greg M

Thank you very much for your reply Greg. Yeah the classes move up as follows:
N4-N5
N5-P1
P1-P2
P2-P3
P3-P4
P4-P5
P5-P6
P6-P7
P7 to be removed.

Any support with how to create a button would be amazing!

Hi again,

Have a look at the attached version of your workbook.

On the "Add or Remove Pupil" worksheet there's a new "Move All Pupils to Next Stage" button. Clicking this will display a "Do you want to continue?" message whose text you'll obviously need to customise.

If the User selects the "Yes" option, all Stage values in Column D are increased by one, except for the N5 values which become P1. P7 values become P8, and are the pupils who have left. You'll probably need to include a "Bulk Delete" facility for those pupils - this is likely to use the existing "RemoveOnePupilFromWorkbook", but with a facility for suppressing the individual "Do you want to continue?" messages.

Anyway, see what you think and please let me have your comments/suggestions.

Regards,

Greg M

This is perfect Greg. Thank you VERY much. Can you explain how to create this so that I can add to master copy. Am I able to copy and paste some of the macro or code from your attached file onto my master version.
Thanks

Hi Greg,

Sorry to bother you again. Yeah that would be great if you could look into possibility of a bulk delete function.

Please can you help me to install the macro that you created. I'm pretty useless with excel- I know what I need it to do but never know how to do it...

I exported 'Module 2' form your file then imported it into my workbook.
However, it doesn't seem to be working correctly- it now says that there is a problem with the name range when I click on it? Any ideas.
The cross reference button doesn't seem to be working either? Not sure if I broke this when trying to install the new macro.

Thanks again for your support.
Kevin

Hi again Kevin,

The attached version of your workbook contains a "bulk delete" feature which will move everyone from his/her current stage to the next stage, and will then delete the records of all of the former P7 pupils who are now classified as P8 pupils. The bulk delete process uses the existing "RemoveOnePupilFromWorkbook" routine, but suppresses the individual warning and status messages.

I would suggest that you copy the names of the pupils from your Master workbook into the attached version, rather than trying to import the code and named ranges from the attached version into your Master version.

Hope this helps - as before, please let me know how you get on.

Regards,

Greg M

Very impressive Greg!! This will save me a lot of time each year.

I have just copied across the names and all now seems to be working great. Thank you very much.

I have also created the same tracking and monitoring for our partner school. Please can you add the same tool to that one also.

Much appreciated,
Kevin

Hi again Kevin,

Thanks for your last post - I'm taking a look at it & hope to be able to respond on Friday/Saturday.

Regards,

Greg M

Hi again Kevin,

The attached workbook for the second school incorporates the features which I already provided in the workbook for the first school. I've also tweaked some of the other code to remove shortcomings - e.g. the "Add Pupil" button would work correctly provided that there was at least one pupil already on the list, but would NOT work when you tried to add the very first pupil. This has now been corrected.

I think You'll need to look closely at some of the worksheets - e.g. the "Pastoral" worksheet contains the names of two pupils which have been typed directly into this worksheet instead of being retrieved (via a formula) from the "Add or remove Pupil" worksheet. The entries in the "Screening Year On Year" worksheet appear to have been sorted in some strange way because the formula references in Columns C and E do not appear to follow the row numbers on that worksheet - e.g. the formula in Cell E4 refers to Cell F8 on the "Add Or Remove Pupil" worksheet.

I also included the same "tweaks" in the workbook used for the first school - I'll include that in a separate post because the combined filesize of the two workbooks would exceed the limit for a single post on here.

Anyway, hope the above helps - as always, please let me know how you get on.

When you're satisfied with what's been done so far, we can consider turning our attention to the second point you raised in your original post.

Regards,

Greg M

Hi again Kevin,

The latest version of the workbook used for the first school is attached.

Regards,

Greg M

Hi again Kevin,

Many thanks for the Reputation increase - much appreciated

Feel free to shout if there's anything else you need.

Best regards,

Greg M