How to make this spreadsheet foolproof?

Hi all,
Season's Greetings

VCAL Outcomes Map 2014 v1.1.0.xlsx (contains test data only) (Excel 2007)

General Issue

I've created this spreadsheet for our community college program, for tracking tasks completed and Learning Outcomes ticked off. The issue is that it's not foolproof, and I've had to explain to the teachers what not to do - the general principle being, 1) if you enter data save it, 2) if you sort the data, print but don't save. The reason is that sorting on one sheet affects correlations on other sheets. Can someone please suggest ways to make it foolproof?

How it Works

Our general guideline is that once we have ticked an outcome 3 times, then we call the student competent for that Learning Outcome. There are exceptions, for example, 2 times in some Outcomes and 4 or 5 in others can give them overall competency based on 'body of work', which is up to the assessing teacher to judge.

The spreadsheet tracks 2 separate things - tasks completed and Learning Outcomes ticked. Teachers and people producing reports need to be able to sort by student, school and student level, each for different reasons (hence, locking cells is problematic). The data for tasks completed creates a report on the 'Individual' page so that it can be given/shown to a student, to show them tasks that have not been completed. We show the student only their data, for privacy compliance.

Entering students alphabetically at the start would help a little, but we do get several students starting with us throughout the year.

Not Foolproof

The problem is that if say the coordinator sorts the 'Outcomes' sheet to produce a report for one of the partner schools (for example), then the flow-on aligns students with the wrong data on other sheets. For now, the teachers of each subject know what not to do, and we'll always keep a backup copy of the most recent update.

Your suggestions for making this foolproof would be most appreciated
Andrew

I'm considering learning VBA.. perhaps by v2 or v3 of the spreadsheet I'll know enough

Originally Posted by xladept

With VBA a change in one cell can be transmitted throughout, maybe Version 2??

True.. but in the mean time.. any ideas for foolproofing this without VBA?