Need help for "People Planner"

[csetchel]

I have been asked to come up with a "people planner" to track which projects employees are on, when their project ends, and future projects/needs for these projects. I have no idea where to start. Here is a rundown of the information that I have and what I have been asked to do:

I have:
Employees (about 200)
Employee Position
Active projects (about 20-40 at any given time)
Employees assigned to each project
Project end date (when employee becomes available)
Future project and need by position
Future project start date

I need:
I need to come up with a tool to track what employees are available (by position) to be assigned to the future project (based off current project end date and start date of future project). Basically, I need a tool to be able to make sure that each employee is assigned to a future project and does not fall into 'overhead'. I want to be able to show current availability and future need of each employee/position graphically so it is easy to understand. A bonus would be if I could come up with some type of way to select a future project and populate a list of employees that will be available of each 'future project position need'. For example, if Future Project A starts on 1/1/15 and needs a project manager, I want to populate a list of project managers who will be coming off of the current projects at this time (plus or minus a month).

ANY IDEAS?!?!?!

[bmouse]

First of all you need data - without data there is nothing to work with, so for starters you're looking for a list of employees with all the info mentioned above. When you have the data gathered and organized, you can do various things with it, based on how well you can handle Excel, or using advice from people on the forum.

From a well organized list you can filter out employees by position and then look at the project dates to see if they're available or not. Without going too advanced, make use of Conditional Formatting to light up the employees that are free in one color and the ones that are busy in another color.

If you cannot post an example file with an exact problem that you're stuck with, its hard to help.

[csetchel]

Attached is a sample of what I am looking at. There will be a lot more data in the final version, but this gives you an idea of what I am looking at and what I am trying to do. Instead of the pivot chart, I could probably do a Gantt chart to show the dates.

[bmouse]

I fiddled a little bit, its far from perfect, but perhaps it will give you some better ideas.. or not.

[csetchel]

Is there some type of If AND function that I could run that may give me what I want?

For example, if I select the project and list out all of the positions required, could I run a IF AND function to populate a list of employees that match the date +-30 days that match the required position? I am assuming that this would require multiple steps and probably a macro, but it may give me what I want. I'm not too familiar with these types of functions, but this is what I tried (with no luck)...

=IF(AND(employee available date column=future project start date-30),employee name column,false)

Edit - I figured out how to get the formula above to work if I select specific cells and the dates are equal. Is it possible to search entire columns? How do I say future project start date plus/minus 30 days instead of equal? I think this is getting me towards what I am looking for!

If I run a macro with this, I should be able to populate a list where all of the employees that match will show up, correct?

[bmouse]

The problem is that a formula can only give you a single result and it cannot pick at random. Meaning, if you need to pick an employee who is available, you would pick virtually anyone, but Excel can't do that (at least to my knowledge). A formula will only give you 1 exact match if there is any.

If the expected outcome is multiple results, then perhaps you need an array formula or a pretty advanced macro but I'm not too familiar with those.

[Bobsone]

Hi
Is the attachment coming close to what you want?
M3:M16 has an array formula that (I think) returns people who will be available before the new project start date in K3.
There is a small issue I haven't resolved yet, the array is designed to work until it has produced as many results as the count formula (N1) indicates. I haven't been able to get the N1 formula to account for the plus/minus 30 days you mentioned. Because I have not been able to get the N1 formula to work I have used a manually entered value in O1.

Another thought,
If you are going to have around 200 names, I would suggest you have a first name and last name column. This is because should you end up with two people with the same last name there is a chance a formula (current or future) may cause a problem by stopping at the first one it finds.

Regards.

P.s. This array can return more than just the persons name, it can return the whole B:F row.

[Bobsone]

Hi.
oops, that will teach me to rush things... It appears excel arrays cant do "and-or" functions
Perhaps one of the talented people has an idea or two.

I have had another look at the problem and have used a combo of array formulas, conditional formatting and a cell (A1) to manipulate the new project start date.

Regards.