How to pin the data contained in a row to a single cell (somewhat like a primary key in Access)

Access is a developers application, not a user-centric application like Excel (arguably) is. Your problem is indeed better handled using a structured system of related tables (which is in fact a database). If you have that, all it would take to change the organisation is change a dropdown of a user from dept A to dept B. The rest would be handled by reporting which is driven by the data. You can (sort of) achieve this in Excel by creating the same table structure and instate relations between the tables. You can then use pivottables to display the current state of things. However, making it easy to edit related tables in Excel is a different matter, it isn't easy at all. This is a lot easier to build in an application like MSAccess.