Email:      Pass:    Pass?
Subscribe for Free Excel tips & more!
E-mail:
Advertisements


Free Excel Forum

Resource Allocation (personnel) Excel Add-on.

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hello,

I am wondering if there is an Excel Add-on (Plugin) which would allow me to do some resource/staff management. I am looking for something that would let me do the following:

1. Able to allocate personnel to several projects. For example 1 person might split half of its time to one project and the other half to a second project.

2. Keep track of time worked by each person on each project

3. Easily scalable to 5-10 projects.

Thanks


Similar Excel Video Tutorials

Helpful Excel Macros

Delete a VBA Module From Excel
- Delete a VBA macro module from Excel with this macro. This macro allows you to fully remove a macro module from Excel.
Format Cells as Time in Excel
- This free Excel macro formats a selection of cells in the Time format in Excel. This Time number format means that inst
Automatically Run a Macro at a Certain Time - i.e. Run a Macro at 4:30PM every day
- Automatically run an Excel macro at a certain time. This allows you to not have to worry about running a macro every da
Create a Bar Chart With a Macro in Excel
- Create a bar chart in Excel with this macro. You will be able to quickly and easily turn any range of numbers and data
Name Worksheets Based on Cell Contents
- This macro allows you to have your worksheets named by whatever is in a particular cell within a worksheet. This means

Similar Topics











Hi,

Please help me to identify a staffing template which I need to use for Resource Management.

I am looking for a Resource Management Template to allocate resources between different projects. (IT)

I would like to capture his/her current project allocation, role, skills, experience, usage etc...when a new project comes I would like to know who is available for what?

Thanks,

George



I am not sure if this is the right area but thought it would be a good start. I have been trying to help my management team come up with an equation for our weekly resource allocation spreadsheet.
Background:
Our team builds a weekly resource allocation spreadsheet for the 30+ projects that we are working on. Each manager is responsible for filling in the Estimated time each of their subordinates will be working that week on said items (i.e. project, training, out of office, tickets, O&M, etc.).
Problem:
Each weeks spreadsheet is saved as a different tab on the master spreadsheet. The equation that I have references originally estimate for project time (how much time we think the project will take) minus the previous week(s) total hours used on said project and then subtracts the current weeks total. So the equation looks like this....='8.18.08'!F13-SUM(I27:AG27).... However, as projects move forward and eventually become completed they are removed from the next weeks sheet. Is there a way that I can have all the projects (which have unique key identifiers) reference PROPERLY across the board even though last weeks item may have been in F13 but is now in F12 due to the removal of a project?

Hope that makes sense.




Good day!

I am working on a spreadsheet to monitor project resource availability. While I can show and indicate overlapping projects and availability by DATES, I need some help with representing the % availability.

Attached is a spreadhseet with the "AVAILABILITY" tab indicating overlapping projects and availabiliity by dates.

I was wondering if anyone could help me with putting a total percentage availability into each timeslot on the "AVAILABILITY" tab.

The "PROJECT/RESOURCE" tab is where I enter the % Allocation per resource. I would like the %s to add up per resource and be represented in each DAY SLOT in the "AVAILABILITY" tab. So along with the colour to indicate projects overlapping, I would like to show the % totals per day as well.

For instance, MICHAEL R has 4 parallel projects, 20% per project...i would like it total 80% throughout the "RED" bar. In some cases, it might go over 100% which is ok.

ANY HELP IS APPRECIATED. TOTALLY STUCK.

THANK YOU KINDLY!

Attached is the spreadsheet.

OK we use a 52 sheet excel work book to track our hours against project numbers 08XX for 2008, 09XX for 2009, 10XX for 2010 etc. Each sheet is named by the week for example May-15.

The challenge is that everyone works on different projects and doesn't use a static list of project numbers (since the list grows everytime a client walks in the door) to put their time against but rather inputs the project number in one cell, then the name of the project in the next cell, then the time for each day which is then totaled for the row (week) in another cell at the end all on the same row.

I'm wondering how we can setup a script to automatically see all the 0828 (for example) project lines in every sheet and grab the total time for that week on that project then give me one big master list of time vs every project that person has worked on in one "total's" sheet at the end of the work book. As it stands it takes about half an hour to go through everyone's excel time sheet work books to prepare an invoice.

Idealy this Total's sheet would list any used project numbers in one column, then have a column for each weekly sheet into which it would place the total time against that project for that sheet so we could still track when the hours were being put against the project but it would take a fraction of the time it does now.

I can post an example of our time sheet book if that helps.


Hi there,

I am programming a project portfolio spreadsheet for resource management and my boss wants to programme in start dates for projects.

This means that the resource usage data for that project will start at the corresponding time in the spread sheet.

For example we use quarters, Q1, Q2, Q3, and Q4. I would like to be able to set a project to show its data fron Q3 onward.

Is there any possible way of doing that??

Thanks
sarah




Good day!

Novice user here.

I am trying to create a 'graphical' view of the IT resources and their availability both WEEKLY & MONTHLY. The IT resources consist of 5-6 roles, with each working on multiple projects with different START and END dates. I want to graphical represent the overlapping days to show if they are UNDER or OVER utilized.

Example:

Project Manger working on 3 projects. 40% time allocated to 3 different timelines. When the dates overlap, I would like the % allocation to total and be represented by a colour if they are OVER or UNDER utilized. For instance, the Project Manger with 40% allocation per project, and 3 timelines could potentially have a 120% utilization, I would like that to be highlighted or showed graphically.

What I would like is a table to enter:

HUMAN RESOURCE
PROJECT
START DATE
END DATE
% Allocated

Each resource can be used for multiple projects, with different % allocated per project, but mostly what I would like to accomplish is a VISUAL view of how each is being over and under utilized.

I think I can graphic them each over a timeline, but i would like them to collapse into one with the % totalling for the overlap.

Does this make sense? I'm willing to pay for any help! things are that desperate.

Attached is what i would like the visual to look like. RED indicating close or over 100% utilization, and YELLOW representing the other end of the spectrum.


THANK YOU, THANK YOU, THANK YOU. ANY HELP IS APPRECIATED. YOUNG AND KINDA DUMB.



Hello All

I'm in a new job where Excel is definitely not by comfort zone. However I am now the resident techie and I spend all my spare time reading excel forums and blogs (30 new functions in 30 days - my new life).

Right - I have a problem I cannot work out.

I have a resourcing spreadsheet. 12 columns one for each month of the year. Resources (people) are allocated to projects in rows. One resource can be on multiple projects. The first 5 columns are project, name, grade, etc. Then the months of the year.

Ideally each person should be not have more than 22 man days allocated across all projects - as they then would be at over capacity.

Each person, for a project, how a number of days allocated against a given month. Eg xx project. fred smith, Jan 10 days. Feb 3 days. March 14 days etc. A project can have between 1 and 30 resources working on it.

What I would like to do is use conditional formatting to make the background go red where you enter the number of days planned where the total number of days for that resources exceeds 22. I can easily do formatting where the sum of days is greater than any given number, but I am struggling with linking this for each resource.

It isn't easy to explain so I have uploaded an example of what I am trying to achieve.

I would be REALLY grateful for any pointers. I have got to the point of going around in circles.

Thanks

Hi there,

I was hoping someone could help me with this. I have tried researching but am not getting anywhere...

Currently I am trying to do a vlookup, to see if I have booked in people for too many hours for any particular day (they are allowed 6 per day but it is tricky because they can be working across several projects).

So it looks something like this:
Project------Name of person allocated-----Monday-----Tues------Wed
Project X---------Person A-----------------------4-------------3-----------4
Project Y----------Person B-----------------------3-------------4-----------3
Project X----------Person C-----------------------6-------------6-----------2
Project Y----------Person A-----------------------3-------------3-----------2
Project Z----------Person A-----------------------2-------------6-----------2
Project X----------Person C-----------------------4-----------2-------------4

The problem is that Vlookup only returns the first item's corresponding cell. Because I need to know if a person has been overbooked for any particular day, I think I need a vlookup that sums all answers together. (I can then do conditional formatting so that if it exceeds 6 then it will show as red/flagged as an issue.

Any help would be greatly appreciated!!

Thanks in advance


i have heard of an add in for excel that enables similar functionality as MS Project. Can anyone point me in the right direction? Ultimately, I am trying to add this to a work in process file that will help us plan and allocate human effort resource hours to projects.


Dear All,


This appears to be a good forum, so in advance I'm thankful for all the information shared here!



I've created a rudimentary portfolio management tool for my client showing, among other things, charts regarding total budget, budget-by-fiscal-year, number of projects, etc.


It works now but my concern is that I need to pass it off to the client (our "transition plan") and from what I know of the person who will take it over for me, she's not very Excel savvy. No problem.


Here's the deal: I have a total universe of projects (on one Excel tab) of 35 projects. (This could grow in the future, though.) Every two weeks a group of executives review 2-3 projects, and when those projects are "approved," I have the go-ahead to include those "approved" projects in my stats (again, budget, etc.). That means that one month ago, my charts only included 6 projects (of the 35). Two weeks ago my charts only included 9 projects (of the 35). Two weeks from now my charts will include probably 13 projects (of the 35).


To manage this I create new Excel tabs with the subset of data that I want to calculate on. Probably not the smartest, but that's how I naively set it up.


As the person who will take this over from me will need something much simpler, my ultimate question is this: *When creating my charts and pivots pulling from the 35 projects, how can I pick-and-choose which projects get calculated into my calculations? (E.g., "This time I want to calculate stats from Project M, Project S, Project T, but not Project U, and not Project X.")


Confused? Probably!


Any assistance would be helpful.


Thanks,






Kelly


I've begun a project to organize the personnel files of an NGO in Brazil into a database, and I need some help with a small part of it. I am not sure if what I want is actually possible, so please feel free to suggest an alternative solution if you feel that this is the case.

Alright, onto business. What I want to do is create a list of all the clients the NGO has and which projects they've worked on and what they did in these projects (skills). Then, when the NGO needs someone with expertise in a certain kind of project, they can simply filter out all those people without that skill.

The problem, however, is that some volunteers have participated in multiple projects and within projects have multiple skills. I can create a new column for every project and indicate the skills they put to use in this project, but then, for example, if I would filter on 1 project for skill A it could happen that a volunteer used skill B for that project but did use skill A on another project. Is there a way around this?

Ideally I'd have one column with projects and the skills people used in these projects, which, when filtered, would retain all the information for the volunteer in question, not just the row that indicates a skill.

If you need any more information, feel free to ask.




I have a number of projects with a time in days allocated. I work on each project, it could be a day/half day/hour at a time. I want to display time allocated in one column, time spent in another and time remaining in another.

Example

Project 1
12 days allocated - 12:00:00 (day,hour, min)
1 Hour spent - 00:01:00 (dhm)
Time remaining - 11:06:30 (dhm)

Working day is 7 and a half hours


I have tried a number of different formulas but cant seem to find one that works. I want to amend the time spent column as I spend time on a project and for it to automatically calculate the time remaining.


Many thanks
Craig

Say I have a table with a list of projects that in column A has the project name ans in B the benefit value and C how many resources. I'd like to be able to return all the projects I can do based on a selected numbe resources available. The look up would first pick off all the higher value projects and as a project is selected reduce the number of available resources before selecting the next project. It has to be able to skip projects tha have a higher value but not enough resources to do the project. So the first criteria is benifit value then availabe resources.


Hi,
I am working on a large chunk of data that is updated every month..for which I intend to use a pivot table.

Objective is simple, get headcount per month. Thing is, resource name appears several times in a month since I also track their percentage allocation. Instead of counting unique instances, the pivot table counts the resource more than once if it is involved in more than one project.

Here is how my table looks like:
Name Project Week Month Allocation Andrew Project A W1 Oct 20% Andrew Project A W2 Oct 30% Andrew Project B W4 Oct 100% Andrew Project B W5 Oct 100% Andrew Project B W1 Nov 100% Ann Project A W1 Oct 80% Ann Project B W2 Nov 90% Ann Project B W3 Dec 100% Lyka Project B W1 Oct 100% Lyka Project B W2 Oct 100% Lyka Project B W3 Oct 100% James Project A W1 Nov 100% James Project A W2 Nov 100% James Project A W3 Nov 100%

Expected result is:
Oct - 3 (andrew, Ann, lyka)
Nov - 3 (james, ann, andrew)
Dec - 1 (ann)

Currently, the pivot table would say Oct - 4 (andrew, adrew, ann, lyka)

What should I do?

Thanks for the help.


Hi,
I am working on a large chunk of data that is updated every month..being so, I intend to use a pivot table.

Objective is simple, get headcount per month. Thing is, resource name appears several times in a month since I also track their percentage allocation. Instead of counting unique instances, the pivot table counts the resource more than once if it is involved in more than one project.

Here is how my table looks like:
Name Project Week Month Allocation Andrew Project A W1 Oct 20% Andrew Project A W2 Oct 30% Andrew Project B W4 Oct 100% Andrew Project B W5 Oct 100% Andrew Project B W1 Nov 100% Ann Project A W1 Oct 80% Ann Project B W2 Nov 90% Ann Project B W3 Dec 100% Lyka Project B W1 Oct 100% Lyka Project B W2 Oct 100% Lyka Project B W3 Oct 100% James Project A W1 Nov 100% James Project A W2 Nov 100% James Project A W3 Nov 100%

Expected result is:
Oct - 3 (andrew, Ann, lyka)
Nov - 3 (james, ann, andrew)
Dec - 1 (ann)

Currently, the pivot table would say Oct - 4 (andrew, adrew, ann, lyka)

What should I do? Any better way to set up the table?

Thanks for the help.


I am using Excel to plot resources and their workloads. Each resource has various projects that have different start and end dates. Each project requires a certain amount of hours from that resource. Based on a 40 hour work week I have calculated the average percent per day that the resource will spend on that project. I would like to be able to see on any given day each resource's workload percentage capacity. Seen below this resource is at 99% capactiy from 15/12/08 to 20/12/08. From 05/01/09 to 31/01/09 they are at 100% capacity. From 01/02/09 to 04/02/09 they are at 124% capacity. From 05/02/09 to 15/03/09 they are at 24% capacity.

I have a second worksheet that uses the above information to give me a calendar view of what each resource is working on, but I can not figure out how to show workload capacity.

See Attachment.

Please help Not sure if Excel can do what I am looking for.




Hi all,

I am working on a resource management type workbook. In the first sheet,(Project Assignment) managers can enter staff, staff type and hours needed for the next three weeks. In a separate sheet,(Total Hrs per week) I have formulas set to total the hours entered from the first sheet for each resource - using the formula =SUMPRODUCT(--(staffassignments=$B4),--(Week1)) for each week, for each resource. That woks fine.

What I would like to do, if possible, is to present a type of data validation, dialog or popup when the resource's "total hrs per week" total = 40 hours. Different managers use the same resources, so one person may have time entered in multiple times for different projects in the Project Assignment sheet.

Is it possible to use the totaling formula (=SUMPRODUCT(--(staffassignments=$B4),--(Week1))) and an if statement in the data validation or conditional formatting to let managers know that the resource is fully utilized?

I can post screenshot if necessary.

Thank you!

I have an application where multiple individuals (5 managers) keep track of their own projects and the individuals they have assigned to work on them in their own Excel spreadsheet files (a total of 5 files). Each managers spreadsheet looks something like the following example:

Column A....

Project Name
Project A
Project B
Project C
Project D

Column B...

Person Assigned
John
Bill
Mary
Bill

I need to make a spreadsheet file that looks at these 5 files and then summarizes all of the projects underneath each Person Assigned. This would look something like the following:

Column A...

John
Project A

Column B...

Bill
Project B
Project D

Column C...

Mary
Project C


Any help on how I could automate a spreadsheet summary file that looks at the 5 Manager files and then lists all projects would be much appreciated.

FYI...I'm using Excel 2003 SP2.

Thank You!


Excel 2010
Windows Vista

I have a project budget file consisting of a summary worksheet for all projects and separate project worksheets. One finance person enters budget allocations to and between the different projects on the summary worksheet (Budget Allocation Table), and project owners enter spends for their project on their specific project worksheet (Project ___ Budget Table). The Budget Allocation Table will be password protected and only finance will make edits.

I am trying to find a way to carry over data from the master 'Budget Allocation Table' to the individual 'Project ___ Budget Tables' for any line item that affects that specific Project Budget.

Assumptions: Budget allocation table will be managed by one person in finance and will be password protected Individual project budget sheets will each be on their own tab. All entries made ONLY in the budget allocation table. Project budget tables update automatically Individual project budget sheets need to 'scan' the budget allocation table and 'pick up' / 'repeat' any budget amount and note that applies to their budget. Macros/VBA are OK (although I am at them), file already has macros and is a .xlsm file type.

I've tried searching these forums, Excel help and on the web, but I'm mostly finding Vlookup type suggestions. I'm sure if I knew the 'name' of the feature that would work here, I would be well on my way to a solution.

Thank you in advance.
Alex









I am working on a Financial Model to be used to keep track of a project financials and would appreciate your help.

Design

1) Assumptions - Durations, scope, and categorical descriptors that can be adjusted
2) Resource List - Input the resources that have been assigned on the project. The resource will also have a start and end date.
3) Forecasting - Allocate a percentage for each of the resource defined in 2
4) Populating - The main sheet will then run a based on the allocation of the resource. Actual figures will need to be manually updated once invoices are received. I will use a reference point which will determine if actuals or forecasts will be used in the overall calculation.


Just curious to know your thoughts on how you would approach this.

In tab 2, the resource will have a defined start and end date that I would like to incorporate into the model. That is if the resource starts in jan 2013 and ends feb 2013, then the resource will be inactive.

The issue I have with this, is if we restart the resource in April 2013 with a different daily rate then my vlookup will reference the resource with jan 2013 to feb 2013 and not the one with the new rate.

Hello. In Microsoft Project, I am trying to run a resource usage report from a master project plan comprised of 10 projects. When i use the standard resource usage report the output is name, task, and hours per week over x weeks. I am looking for a report that will show me name, project and hours per week over x weeks. Is there a way to set up a custom report to display project name instead of all the specific tasks within a project?


I've been charged with enhancing the existing staffing chart we use on a weeky basis. Currently, we track multiple projects (30+) and 50+ staff members who often work on more than one. We typically work in 8 hour units (though sometimes we note a 2 or 4 hour block if only limited interaction is required), but we have no way of tallying and individula person's responsibilities without counting up the hours listed next to their name on each of the 4-5 projects they might be working on. We would like to keep our 430 project tracker to a single 11X 17 document, so creating a matrix per person on each project isn't feasible. I'm wondering if there's a way to tally everytime I see Bob's name and his associated hours - would a pull down menu work well for this? Any advice would be greatly appreciated.

i have a pivot table that calculates resource availability. each resource
may work on multiple projects, and each of these projects require a certain
number of hours. the grouping is by person, by project. for each person, i
need to show hours allocated toward each project (detail) and then total
number of hours allocated (subtotal). i also need to show a calculated field
of % hours available (subtotal only) for each person.

John project 1 30 hours (detail)
project 2 15 hours (detail)
Sum of hours 45 hours (subtotal)
Caculated field (1-sum(hours)/40) (subtotal)

how can i show this calculated field only as a subtotal but hide its detail?

thanks much.

--
mt



I have used Excel to create a staff schedule for our employees. It is pretty simple; I have dates across the top as column headings and employees as the rows. It has nothing to do with hours, as we really only work on one project per day. So the only thing in each cell is the name of a project.

Is it possible to run a quick project summary that would just tell me what dates that project is being worked on? It would just have to search the entire table for the name of a project and then kick back to me the dates from the top row where that name is found. I don't think it should be that hard! We have less than 10 employees but 50+ projects that we work on, so it needs to be something I can replicate easily for all our projects.

Thanks!


I am creating a workbook for project managers to manage the projects various teams have active. Once a week we receive a report that is pulled from a CRM (customer resource management) like tool we use to track progress on a day to day basis. This report contains the data for all project managers and I am creating a workbook for each of them to show just their projects at a glance.

Each project manager has a number of teams and each team could have from one to many projects ongoing and active.

What I am trying to do is this:

Lookup the name of the team Determine if there are active projects for that team Return the oldest project completion date for that team Be able to cycle so that when a project is completed the next oldest date is displayed and so on. From the conditional point of view, I have tried using VLOOKUP in a nested IF statement which obviously only returns the first instance in the list based on the conditions. I have looked at the MIN and SMALL functions but can not figure out how they would fit into this formula.

I am stuck and need help to solve this. PLEASE if anyone can help or try to point me in the right direction, it would be greatly appreciated.