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

[Joe Wightman]

Situation

I'm currently trying to improve an excel document for work.  The spreadsheet details all the employees personal information, qualifications and attendance to key events etc.  The information on each employee is distributed through different sheets, for ease of access.  The issue is that the employees are regularly re-organised into different 'teams'.  My boss uses the 'team' that a member is in at that given time as the key reference point when browsing the spreadsheet.

Problem

Is there way to have a directory on a sheet, within the document, detailing the current organisation of the company with regards to 'teams'?  This would allow myself or my boss to move members up or down into different teams; a move that would then be reflected in all the other sheets of the spreadsheet, moving all the related info in subsequent columns with it.

Sidenote

I understand this may be something which could be easily address by producing a database with Access however I'm trying to avoid that due to the workload and the fact my boss is already so technologically un-savvy that he's delegated all spreadsheet work to me already, so he doesn't even know what access does, let alone how to navigate and use it efficiently.

Kind regards,

J

[#2]

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.

[#3]

Mr Pieterse,

Thanks for the response.  I had better start refining my MSAccess skills...