I suggest the following approach which presents a member of staff's record to the user when their name is selected from a combobox.
Add a new worksheet which is linked to each departments worksheet.
The columns in this 'All Depts' worksheet are the same as the columns used in the departmental worksheets.
For this example I had departments named Dept_A, Dept_B and Dept_C
Go to each departmental worksheet and add a column to the right of the name and enter the following formula in cell B2 (row 1 has headings, column A is names.
=A2 & " " & "Dept_A" (the department's name), drag down all used rows and a few extra rows to allow for new staff, and repeat for each departmental worksheet. Column B is now a unique name - department combination.
Then copy all the used cells and include a number of empty rows below the last used row to allow for new staff in each department.
Paste Special, Paste-link to the 'All Depts' worksheet.
In the next empty column in the All Depts worksheet create a column labeled 'Sort'
If this is column G enter this in G2 =IF(A2=0, "ZZ",A2) assuming that column A contains the staff names.
Now Sort the 'All Depts' worksheet based on the Sort column.
The spare rows will sort to the bottom.
Now 'name' the second column of data (name and department combined)
Select the cells in column B, excluding the header row, but including the rows with zeros (so that new staff will be included later). Goto Insert - Name - Define. In the dialog box name the range 'StaffList' (no spaces allowed) and click Add, then OK.
On your main worksheet with the drop downs, use the named range 'StaffList' as the linked list for the ComboBox.
In design mode, right-click Combobox, select Properties
ListFillRange enter StaffList
LinkedCell enter D5 (or another cell - it's not critical)
Close properties box and exit design mode
The drop down will now show names and departments - so allowing correct selection of staff with same or similar names in different departments.
The staff name will appear in cell D5
To display all the staff members details enter this in cell E5
Adjust the lookup range in column B as required.
Drag this to the right and change the column offset numbers.
As a name/department is selected, the record for that individual will now show in cells D5, E5 etc.
This doesn't actually take you to the original record, but it does present the record to the user, which I trust is a satisfactory alternative. If not, let me know. To go to the actual record on a departmental worksheet will require a macro.