Excel tab name repeated in sheet cell

To streamline my student's research report - or article - marking at the end of their projects, I use a rubric set up in a spreadsheet. Not only does this spreadsheet outline all the items I am seeking within my students' work - so performing the function of a checklist for them in preparing their document, but it also totals the ticked items, section by section. It also makes marking much, much easier.

Once marked, I pdf each sheet and upload it into Moodle with the student's mark. I don't provide detailed feedback at the end of the course: as a supervisor, there have already been many weeks of conversation about what constitutes 'quality' work, supported by constructive feedback, exemplars and experiments. What students need now is a mark, and moderators will need an indication of how the learning outcomes have been allocated and marked against.

Additionally, I seek permission each year from students who have gained an A grade to share their research reports or articles with the next cohort of students. I encourage current students to examine past student's work to see where it was strong, and where it could have been improved, using my marking sheet, which is appended to those past submissions.

While the marking sheet works well, I am always looking for ways to reduce administration further. Recently I was setting up my marking sheets for the end of the semester, and wondered whether, if I named all my spreadsheet tabs with my student names, whether each sheet could then pick up the student name in the "name" field at the top of the spreadsheet. To my delight, after a quick google search I found that I could indeed do that, using a fairly simple formula:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

All I have to do now is to appropriately name each tab for each student, and their name now automatically appears in the - now - protected name field.

It works a treat!


Sam

• Reference: ExtendOffice (8 May 2017). Reference the current sheet tab name in cell with formula. Retrieved 25 October 2017 from https://www.extendoffice.com/documents/excel/2618-excel-reference-tab-name.html

If anyone wants to see how this spreadsheet works in action, you can download a copy here. Please note that, while the spreadsheet is protected, there is no password.