Linking and sharing excel documents

%3CLINGO-SUB%20id%3D%22lingo-sub-1594234%22%20slang%3D%22en-US%22%3ELinking%20and%20sharing%20excel%20documents%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1594234%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20hoping%20someone%20can%20help%20me.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20a%20secondary%20school%20teacher%20and%20trying%20to%20create%20a%20master%20marksheet%20on%20excel%20which%20hold%20all%20the%20students%20data%20for%20my%20course.%20I%20would%20then%20like%20to%20create%20separate%20marksheets%20for%20each%20student%20which%20I%20will%20share%20with%20them.%20I%20would%20like%20to%20link%20the%20data%20from%20my%20master%20marksheet%20into%20their%20personalised%20marksheet.%20This%20means%20each%20student%20can%20only%20see%20their%20own%20marks%2C%20but%20I%20can%20enter%20them%20in%20once%20in%20one%20central%20place.%20This%20saves%20lots%20of%20time%20and%20the%20students%20have%20continually%20updated%20electronic%20markbooks.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20managed%20to%20create%20these%20documents%20and%20the%20links%20between%20them%20but%20I%20can%20only%20get%20the%20links%20working%20in%20the%20opposite%20way.%20Therefore%20only%20be%20able%20to%20enter%20the%20marks%20in%20each%20students%20mark%20book%20and%20then%20they%20all%20feed%20back%20into%20my%20master.%20This%20is%20a%20lot%20more%20time%20consuming%20as%20I%20have%20to%20open%20each%20students%20mark%20book%20to%20enter%20in%20their%20marks%20for%20each%20test%20etc.%20I%20have%20tried%20to%20reverse%20the%20links%20but%20the%20students%20do%20not%20see%20the%20updated%20document.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20anyone%20know%20what%20I%20am%20doing%20wrong%3F%20Is%20it%20my%20sharing%20preferences%2C%20or%20link%20preferences%3F%20I%20would%20be%20really%20grateful%20if%20someone%20could%20help%20me.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1594234%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1595430%22%20slang%3D%22en-US%22%3ERe%3A%20Linking%20and%20sharing%20excel%20documents%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1595430%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F762387%22%20target%3D%22_blank%22%3E%40Scross2020%3C%2FA%3E%26nbsp%3BThis%20could%20be%20done%20by%20using%20a%20pivot%20table%20with%20a%20filter%20by%20student.%20Then%20you%20can%20create%20separate%20tables%20for%20each%20student%20in%20the%20blink%20of%20an%20eye.%3C%2FP%3E%3CP%3EIf%20you%20could%20upload%20an%20example%20file%20%26nbsp%3Bwith%20(anonymous)%20master%20data%20and%20the%20summary%20by%20student%2C%20I%20believe%20it%20can%20be%20made%20fairly%20easily.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1598476%22%20slang%3D%22en-US%22%3ERe%3A%20Linking%20and%20sharing%20excel%20documents%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1598476%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20very%20much%20for%20replying.%20I%20have%20attached%20two%20documents%2C%20one%20of%20the%20master%20marksheet%20where%20I%20would%20like%20to%20enter%20the%20data%2C%20the%20other%20is%20the%20student%20marksheet%20which%20I%20would%20like%20to%20share%20with%20the%20student.%20I%20would%20like%20this%20sheet%20to%20read%20from%20the%20master%20sheet%20but%20when%20sharing%2C%20not%20allow%20the%20student%20to%20change%20the%20data%2C%20only%20read%20it.%20I%20hope%20that%20makes%20sense.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1598553%22%20slang%3D%22en-US%22%3ERe%3A%20Linking%20and%20sharing%20excel%20documents%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1598553%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F762387%22%20target%3D%22_blank%22%3E%40Scross2020%3C%2FA%3E%26nbsp%3BThanks%20for%20sharing%20this.%20Now%20that%20I%20see%20what%20you've%20created%2C%20my%20idea%20might%20not%20be%20what%20you%20had%20in%20mind.%20The%20attached%20workbook%20takes%20your%20data%2C%20but%20in%20a%20different%20structure.%20A%20summary%20is%20created%20in%20a%20pivot%20table%2C%20which%20in%20itself%20is%20not%20very%20useful.%20But%20from%20the%20Summary%2C%20the%20pivot%20tables%20per%20Student%20are%20created%20very%20easily.%20Perhaps%20you'll%20find%20inspiration%20in%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1599786%22%20slang%3D%22en-US%22%3ERe%3A%20Linking%20and%20sharing%20excel%20documents%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1599786%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20very%20much%20for%20trying%20anyway.%20The%20system%20I%20have%20created%20does%20work%20but%20I%20have%20to%20input%20the%20data%20into%20each%20students%20file%20rather%20than%20one%20file.%20I%20did%20try%20it%20this%20way%20but%20it%20wouldnt%20update%20for%20the%20student%20unless%20I%20opened%20the%20file%20and%20re-saved%20it.%20I%20just%20cant%20figure%20it%20out.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20used%20to%20use%20Google%20Classroom%20and%20it%20worked%20brilliantly%20but%20now%20we%20are%20not%20allowed%20to%20use%20it%20anymore%20which%20is%20such%20a%20shame%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I am hoping someone can help me.

 

I am a secondary school teacher and trying to create a master marksheet on excel which hold all the students data for my course. I would then like to create separate marksheets for each student which I will share with them. I would like to link the data from my master marksheet into their personalised marksheet. This means each student can only see their own marks, but I can enter them in once in one central place. This saves lots of time and the students have continually updated electronic markbooks. 

 

I have managed to create these documents and the links between them but I can only get the links working in the opposite way. Therefore only be able to enter the marks in each students mark book and then they all feed back into my master. This is a lot more time consuming as I have to open each students mark book to enter in their marks for each test etc. I have tried to reverse the links but the students do not see the updated document. 

 

Does anyone know what I am doing wrong? Is it my sharing preferences, or link preferences? I would be really grateful if someone could help me.

 

Thanks in advance.

4 Replies

@Scross2020 This could be done by using a pivot table with a filter by student. Then you can create separate tables for each student in the blink of an eye.

If you could upload an example file  with (anonymous) master data and the summary by student, I believe it can be made fairly easily.

@Riny_van_Eekelen 

 

Thank you very much for replying. I have attached two documents, one of the master marksheet where I would like to enter the data, the other is the student marksheet which I would like to share with the student. I would like this sheet to read from the master sheet but when sharing, not allow the student to change the data, only read it. I hope that makes sense.

@Scross2020 Thanks for sharing this. Now that I see what you've created, my idea might not be what you had in mind. The attached workbook takes your data, but in a different structure. A summary is created in a pivot table, which in itself is not very useful. But from the Summary, the pivot tables per Student are created very easily. Perhaps you'll find inspiration in it.

 

@Riny_van_Eekelen 

 

Thank you very much for trying anyway. The system I have created does work but I have to input the data into each students file rather than one file. I did try it this way but it wouldnt update for the student unless I opened the file and re-saved it. I just cant figure it out. 

 

I used to use Google Classroom and it worked brilliantly but now we are not allowed to use it anymore which is such a shame