Project Management - Identifying Employees assigned to a given task and client

windsorville

New Member
Joined
Nov 23, 2013
Messages
2
INTRODUCTION
This is an attempt at creating an excel database for work that will allow employees to identify who is handling a particular task for a given client. Excel is pretty much the only option since most employees are familiar with the application and almost no one knows how to use MS Access or SQL to query a database. In fact, most people don't even like using excel either, so I need to make this as simple to use as possible.

BACKGROUND
A note about my office just to give a little background for my particular question, but I am going to try to keep my actual question as broad as possible for future search reasons in case this comes up for someone else. My office is structured by teams. Right now, each team has their own list of clients and the employees that handle the clients for their specific team. It is a pain to have to go through and find each one of those documents and find out who is handling a client for a specific team. I'd like to compile this all on one spreadsheet with a little interface for ease of use. Each team handles a particular set of tasks in a project. All teams' tasks together complete a project. The company has many clients. An employee is a member of only one team. Each client is handled across all teams, however only certain employees on the teams handle that specific client, and each employee handles multiple clients for the team. For simplicity, I will refer to the teams at my company as "tasks" going forward.

For example:

Clients Tasks Employees
ABC Co. Mail sorting John Smith
ABC Co. Management Steve Jones
ABC Co. Data Entry Mike Adams
BCD Corp. Management Jane Doe
BCD Corp. Mail sorting Kevin Brown
BCD Corp. Data Entry Michelle Adams
XYZ Inc. Mail Sorting Kevin Brown
XYZ Inc. Management Steve Jones
XYZ Inc. Data Entry Michelle Adams

So in the example, we have 3 clients, 3 different tasks, and 6 employees. Michelle Adams and Kevin Brown both perform the same tasks for two clients, the rest of the employees handle only one client.


DESIRED RESULT
I want to create an excel database to house this data. There will be a form interface on one of the sheets where users can enter the task and the client, and it will return the name of the employee responsible.

In my mind, I would like for it to look and react similar to this website with the ability to start typing the word in the search box and have it autocomplete or at least narrow the results, NFL Start Sit Tool - Who Should I Start or Sit? however I'm realistic and realize it will look much less polished. The website I linked compares two selected NFL players and suggests which one is better suited to start on your fantasy football team. I want to skip the comparisons, and go straight to the result. So instead of matching individual categories, I just want to check specific criteria (namely the client and the task) and return the best match (the employee responsible for that task and client). I would like to make it a web based app down the road, nicely polished and customized further, but I am ok with just using excel in the meantime.


QUESTION
What is the most efficient way to set this up to minimize errors? I could either set it up as a database format, with separate tables for clients, employees, and tasks, and then tables incorporating IDs to link them, or one giant spreadsheet with every client listed multiple times like in the example above. I'm thinking that if I use the database, it will be easier to update as employees are terminated or leave and are replaced with someone else, as clients are rearranged within a given task for employee workload balancing purposes, and as clients are added, eliminated, changes its name, or is merged with or acquired by another client. In this cases I would likely use the INDEX and MATCH lookups, right?

If I use the large table option, I think it would probably just use VLOOKUP. Not sure which way is the most efficient way to achieve the end result I am looking for. If there are other options out there that I am overlooking, I'm all ears.
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
11,005
taskmngmnt
clientdef
clienttasknameCount of name
abcmailsortjohnnameTotal
abcmngmntstevejane1
abcdata entrymikeGrand Total1
defmngmntjane
defmailsortkevin
defdata entrymichelle
xyzmailsortkevin
xyzmngmntsteve
xyzdata entrymichelle
task>>>>>>>>>mngmnt
client>>>>>>>>def
##############jane
a very simple pivot table where the user
selects mngmnt from the list of tasks
and def from the list of clients
jane is referenced from the pivot table

<colgroup><col><col><col><col><col span="3"><col><col><col></colgroup><tbody>
</tbody>
 

windsorville

New Member
Joined
Nov 23, 2013
Messages
2
That's definitely one way I hadn't thought of, but it's not quite what I had in mind. It will work in the meantime as a quick and easy solution, though.
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows
How about just AutoFilter?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,176
Messages
5,600,140
Members
414,365
Latest member
UUR

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top