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.

NotHackingYou

Why not export the excel speadsheet to XML and make a quick and easy web app in .NET that displays via a gridview and allows changing the XML?

Claymoore

Do you not have Exchange? This is the whole purpose of the Global Address List.

CodeBlox

+1 for the GAL

In addition to that we have an intranet site similar to what CarlSaiyed suggests.

About7Narwhal

GAL is nice... or a simple database tied to a web front end.

the_Grinch

Problem is the University controls exchange and they don't populate the phone numbers. I don't believe I have a web server I could host it on, which is the other issue.

chmod

crrussell3 wrote: »

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.

Good solution.
I had this problem once and i did something very similar.

the_Grinch

True hadn't really thought to do it that way, but I think that could definitely work! Thanks all!