Data taken from other worksheets get messed up upon sorting

Hi everybody!

I have been using Excel for simple calculations of scientific data sheets for many years, but have no special knowlege of the program. Now my daughter (who is a school teacher and has never used Excel before) asked me to make her a sheet for calculation of marks. I organized the workbook in 6 sheets, 4 quarters and 2 tables for the certificates after halfyear and final. The names of the kids as well as the data for calculation of the final marks in the 2 certificates are taken from the scores in the quarter sheets. So far, everything works fine. However, when the list in one sheet is sorted, either by the names of the kids or by their scoring, the resulting certificate sheets get messed up.

Obviously, I would need a way to make the reference to the fields in other sheets relative and not absolute. I searched the www for hours and found some discussions of the problem, but unfortunately I don't understand anything due to lack of experience with Excel. I uploaded the file as an attachement, but didn't mange to include it here. Clicking on the link icon results in an empty white field. So I uploaded the file to my cloud and provided the link, resulting in an error message telling me that I am not allowed to post links before having posted other stuff. No idea what I could post except my question.

Is there a simple (!) way to tell Excel to stick with the reference fields in other sheets after sorting? Please apologize if the answer to my question could be found somewhere else and I didn't realize it. If so, reference to relevant posts or other sources would be helpful. But consider that I need it simple.

I would highly appreciate your help! Thank you in advance!

Edit: The attachement is there! Please apologize German language in the file, but I didn't want to mess up anything by changing names. The certificate sheets are HJZ and JZ (Halbjahreszeugnis and Jahreszeugnis).

I would use the vlookup function to make sure that the structure is maintained in the two sheets

For example in HJZ in B2 =(VLOOKUP([@Name],Tabelle1[@[Name]:[Note schriftlich 1 ]],4,0)+VLOOKUP([@Name],Tabelle2[@[Name]:[Note schriftlich 2]],4,0))/2

Use this example to modify the rest of the cells.

The 4 means that you want the data from column 4 of the lookup range. The zero tells excel to find the exact item. Here is tutorial to help you. Can't let your daughter know that you are stumbling. We will get you to where you need to be. Don't give up yet.

https://www.techonthenet.com/excel/formulas/vlookup.php

Post back if you need more help

Alan

This might be helpful to you: https://en.excel-translator.de/