Using lookups in mark-grade conversion (moving over from google drive)


Hi there,
I’m a teacher and would love to move us over to airtable for our data tracking within our department.
We have been using this Google Sheet for the last few years but it’s clunky. Teachers can only filter, not sort, their students because it throws all the look ups out of order. Previously I’ve had some of these background formulae in hidden columns in the main sheet, which worked better in that you can data sort, but was much worse in that it was harder to add assessment points and the whole thing was more confusingly structured.
The other issue is that although I put warnings on columns people shouldn’t touch etc, it can easily be overwritten and thrown out of whack by a mistaken click from someone who doesn’t know what they’re doing.
We have two of these sheets in operation at a time (2 GCSE year groups - UK 14-16 exams), with 180 students listed in each and up to 9 teachers using it.
So you can see there’s lots of opportunity for it to be screwed up!! I also need to make it future proof - the members of the dept are not v technical and I am the only one who knows how to make this at the moment which is not a clever scenario…
I’ve added a couple of dummy students so you can see how it’s supposed to work.
Airtable looks a lot more stable but I can’t figure out the best way to make it have the same functionality. If anyone can help I would be super grateful!