Excel Employee Directory

the_Grinch

Today the VP requested that I make sure the employee directory is up to date so that we can send out an email to begin testing. Currently it is housed in an Excel spreadsheet. We have two sheets, one containing a list of all employees (First Name, Last Name, Telephone, Mobile, Email) and then a sheet that has all the same info, but listed by department then sorted alphabetically. Of course, each time we get a new employee we have to manually enter them (twice). I felt this was stupid and am pretty sure I should be able to make it so we enter them on the All Employees sheet and then let excel automatically update the by department list. Any hints on how to do this?

On the new master list, I created a drop down box with the eight departments that we have (thus it is First Name, Last Name, Telephone, Mobile, Email, Department). I figure on another sheet I can list those 8 departments and dynamically update it when we enter a new person on the All Employee list.

crrussell3

Why not go easy and do AutoFilter? Make a header row such as Name Department Phone etc etc and click on the department cell. Goto Data -> Filter and click on it. Profit. Now its a clickable filter list.

You could use Data Validation to keep constant strict values for Department Names, so no one puts in I.T., IT, Information Technology, etc.