Resource Allocation (personnel) Excel Add-on.

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.

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.

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

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.

Greetings all, having another issue.

First, let me preface this by saying that I'm not very familiar with pivot tables, but I suspect this actually applies to any general formula as well.

The sheet has a series of columns tracking the time a person spends on a particular project. Column A contains the person's name (in no particular order), column B contains the splitcount (more on that later), and column J contains the last date the person will work on a project. This sheet keeps historical project data, so data for projects that have concluded are still retained (therefore, their column J would be less than TODAY())

My question regards the splitcount (column B), which should be 1 divided by the number of active projects if the project on that row is active (TODAY() < column J), and 0 if the project is not active (TODAY() > column J.)

My formula at the moment is (for cell B5):

=IF(OR(H5<>"WEB",AND(J5<>"",TODAY()>J5)),0,1/COUNTIF(A:A,A5))

So far, I have gotten the conditions working to return 0 for an inactive project. The conditional statement before COUNTIF is correct, so it can be ignored.

Currently, the COUNTIF is counting (correctly) the total number of projects that the person in A5 has ever worked on. I need to add a condition that will then check the J column for each project to make sure it is active: OR(J5 = "", TODAY() < J5)

I have tried making the COUNTIF as follows:
COUNTIF(A:A,AND(A5,OR(J:J = "", TODAY() < J:J))

Neither confirming it normally (enter) or as an array (shift+ctrl+enter) has worked so far.

Any ideas?

Hello - I'm new to posting to the forum because it's been so useful in answering my questions in the past and I haven't needed to post a question. So thanks to all that have unknowingly helped me in the past!

I want to setup a Data Validation rule on a cell which will use a list. I'm familar with doing this and have used it many times before, however my new scenario is unique and I'm having trouble finding a solution.

Say I have a list called "Projects" which covers 3 cells (in a column, if that matters) that contain "Project X", "Project Y", and "Project Z".

I set up a Data Validation list on a cell (call it the "Project" cell) so that I can choose from one of these projects in that cell using a drop-down list.

I have a cell next to this one called "Sub-Projects". I'd like to setup a similar Data Validation rule using a list whose list content would change based on the selected value in the "Project" cell. I've set up a similar list where by I may have two columns like this:

Project X | Sub-Project A
Project X | Sub-Project B
Project X | Sub-Project C
Project Y | Sub-Project D
Project Y | Sub-Project E
Project Y | Sub-Project F
Project Y | Sub-Project G
Project Z | Sub-Project H

I want the "Sub-Project" cell to effectively look up the value used in the "Project" cell and use the list of sub-projects as the source for the Data Validation rule.

For example, if I select "Project X" in the "Project" cell, I'd want the drop-down list in the "Sub-Project" cell to only contain the values "Sub-Project A", "Sub-Project B", and "Sub-Project C".

I add to both the "Projects" and "Sub-Projects" list frequently, so I want a dynamic approach to specifying the source list for the "Sub-Project" data validation rule that doesn't require me to update it each time I add a new Project or Sub-Project.

Any ideas?

Thanks again for all the help, past, present, and future!

I'm trying to use a Pivot Table while referencing a list that is all text.

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!

I am trying to do the following:

(1) Lookup Unique Resource ID
(2) then Lookup Unique Project ID
(3) then Lookup the Month for this resource
(4) Then Lookup the hours associated with the resource, multiple
Project IDs, and month
(5) Sum the resource hours for that group of projects and the
month

This data comes from a pivot table. Note: the resource and Project IDS are not part of the pivot table. I added them to assist in the lookups.

Can anyone please help.

I have a spreadsheet which adds up the total hours worked by a person on different projects each week at the top of the sheet, using SUM

Total
Emplyee Time
Employee A - =SUM(...
Employee B - =SUM(...
Employee C - =SUM(...

Project 1
Employee Time
Employee A - 2
Employee B - 1
Employee C - 1.5

Project 2
Employee Time
Employee A - 2
Employee B - 2
Employee C - 1

The space between the row where the employees time is recorded is not always the same (it doesn't go Q4+Q8+Q12...) it goes (Q19+Q34+Q48+Q59....and so on)

I have a macro that adds in the blank details for creating a new project (so rather than the project name it will say "project name here", ready for the user to fill in.....)
this is inserted just below the totals, always the same place.

So, when the new project is inserted, the rest of the projects move down, keeping the cell references of the sum(.... intact, everything just gets x rows added to it.

What i want to do is add the cell of the new project to the fomula at the top - so it will be all the old cells, with new numbers as a result of rows being added, AND the new cell of the new project.

The next time a project is added, the previous addition will get moved down as well and the formula will get updated with the new cell of the new project once again.

does anybody know how to write a macro that will do this?

apologies for my terrible explanation, I am very much a novice at Visual Basic.

Hi

I've been trying to find a way to write a VBA program to calculate the optimal solution for allocate projects to a number of different people based on their preferences, but I have had no luck.

For example, I have ten projects that need to be allocated to ten people. Each person states their 1st, 2nd and 3rd preference which are then turned into scores (1st=30, 2nd=20, 3rd=10).

The objective is to allocate the projects to people in such a way that maximises the overall score. So, for example, if everyone was allocated their 1st preference the total score would be 300 (the maximum possible).

What I have been trying to create is a VBA program that could determine the optimal allocation of projects based on preferences, generating the highest total score, which ensured each person was allocated only one project each.

Any ideas would be most welcome!

Thanks
Matt

Hi All!

I have a sheet where several people enter time spent on a particular project each day.

Under "Totals", I have drop down validated cells to choose:
Person's Initials
Project Name
Week

My formula for Total Hours for selected Person for all Weeks, all Projects:
=LOOKUP(U279,AH13:AH30,AG13:AG30)
works fine.

My formula for Total Hours for Person for Selected Project:
=SUMIF($AI$11:$AI$264,($U$279&$Q$275),$U$11:$U$264)
works fine.

Now I need a formula for both:
Total Hours per Person for selected Week and selected Project
Total Hours per Person for Selected Week ALL projects.

U279=Initials/Persons
Q275=Project
R271=Week

Any help?