I am building an Excel spreadsheet for tracking a large number of personnel's daily attendance records. All data examples shown here are fictional, including the data structure. Only the gist of the data structure is shown, for illustrative purposes, as I am working on sensitive personal data on heavily locked down computers (i.e. no Internet, any form of scripting like VBA is banned, only Microsoft Office suite to manipulate data).
The main table for containing personnel records looks something like:
Master List
ID Name Home Address Phone Number Remarks
1 Alice 12 Somewhere Rd. 123456 remark 1 goes here
2 Bob 23 Someplace Dr. 234567 remark 2 goes here
3 Charlie 34 Somewhen Blvd. 345678 remark 3 goes here
As one can see, not all the information is required during attendance taking. As such, I perform a Power Query to slim it down to the essentials
Attendance
ID Name Remarks
1 Alice remark 1 goes here
2 Bob remark 2 goes here
3 Charlie remark 3 goes here
After that, I appended the attendance entries to the right of the table.
Attendance
ID Name Remarks 2020/6/25 2020/6/26 ...............
1 Alice remark 1 goes here P P ...............
2 Bob remark 2 goes here P VL ...............
3 Charlie remark 3 goes here P P ...............
Finally, I set up a self referencing table such that upon every refresh of the Attendance table, the attendance entries are preserved in their original rows, meaning that there would not be any problems if I insert a personnel somewhere in the middle.
However, I quickly ran into an issue when I realized I needed to tag every single special attendance entry (e.g. medical leave) with details regarding the leave itself. I initially tried adding comments onto the attendance cells directly, but realized that the comments does not follow the table generated by the Power Query (i.e. it stays in the original absolute cell location, meaning that an insertion of a new person will throw the bottom half of the table out of alignment by exactly one row).
I later attempted to use hyperlinks to link each attendance entry to a separate table which contained the remarks/details about the leave, but the link became unclickable after a query refresh.
How should I go about designing a remarks system for a per-day-per-person attendance system, and is my approach in the start where I query the master list, reduce the columns and then append more columns even the right approach to begin with?
Thank you!