The situation is as follows:
- I need to create an Excel file to use as a template for my students, but this file queries a database generated by another Excel file. However, some students can access the database, while others cannot, and the access is quite inconsistent.
The first file is used to generate all possible answers (the database).
- These answers are unique for each student and are generated from a unique set of input data each semester.
- The file is located in the Example folder within my OneDrive.
- Students should not have access to the database file.
The second file contains the template structure, allowing you to see if the answer is correct or not.
- The student enters their group number in the designated location.
- Then the student enters their answers in the specified locations.
- Next to the student answer, there is an IF formula, that searches with an LOOKUP the correct answer data on the first file and compare with the answer of the student.
- The reference used in the LOOKUP function is a link to the file on OneDrive Personal.
- If the answer is correct, the cell next to it turns green; otherwise, it remains red/orange using a conditional formating.
- The correct answer from the database is never displayed.
- The file is located in the same folder as the first one in my OneDrive.
When sharing the second file with students directly through the sharing link generated by Excel, several situations occur:
- Some students, when opening the template, the LOOKUP function can access the database and check it.
- Some of them can do so after downloading a copy of the file and opening it in the Excel application.
- Others can do it using Excel online.
- Some can do it on their mobile devices but not on their computers.
- Some can do it when their Excel is linked to a Microsoft account, while others don't need this.
- Others cannot access it through any of the above alternatives.
As error messages appear: server error when looking on the Querries and connections:
In other words, I have a very inconsistent system for using this.
I need a more reliable alternative or a solution...
Note: It's not possible to keep the database in the same file as the template for the following reason:
- Even though it's possible to hide the database and protect the spreadsheet structure with a password, simply opening the file in Google Sheets reveals all hidden sheets, and the protection password becomes ineffective.
- When using a second file, even in Google Sheets, students cannot access the file.
I'm using:
Microsoft 365 - Family
OneDrive Personal
Excel version 2311 build 17005.20000
Already tried Power Query, but didn't find a way to get data from it without importing the full data table in the file.
I can provide links of Example files if requested.
