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.
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.
