Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



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

View Answers     

Similar Excel 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







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.


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 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've looked around for what should be a simple answer. (BTW - what a great site this is, wow!)

- I am a project manager working in small/mid sized ERP implementation (not relevant, but throwing that out there)

- Using Excel to track projects, Projects is to expensive for what I do

- OK, FTE is Full Time Equivalent. If a Task takes 1 day to do and Sally is only working 4 hours a day on the project, the FTE of this task should be 2 days. It'll take Sally two days to do a one day task.

- What I know is this:
- Tasks all take X days to accomplish, this is provided by the slavedriver...errrr... project manager (me)
- Sally has a real job and can only devote 50% of her time to the project. Take Bob, he is only willing 10%. It'll take him (????) days to complete the 1 day task.

- So, this is simple. number of days of the Task * 8
- % the person is available to work on the project (let's call it "availability"
- divide the number of hours by "availability".
- round up to the next number


MY QUESTION:

Do any of the Project Managers out there have a better calculation than the idiotic one I have? I'm missing something....


Mucho Thanks,

That new guy...


I'm having a curious problem I'm hoping someone can help me with...

When I was using Excel 2003, I would often 'lock' my VBA projects when sharing workbooks with sensitive information... it was especially helpful when I had information on a Very Hidden tab that I didn't want anyone to know about or try to 'Unhide'. However, ever since I've been using Excel 2007 and 2010 I have been unable to lock any projects prior to sharing or distributing. Here are the steps I've been using to try to lock my projects in Excel 2007 & 2010: In the VBA window I highlight the workbook/project I'm interested in locking. I click on 'Tools > VBAProject Properties...' and click on the 'Protection' tab. I mark the box called 'Lock project for viewing' and then provide a password and confirm password. Once done, I either save the workbook from the VBA window or I navigate back to Excel and save the workbook from there. Either way, when I re-open the workbook it's as if I never protected (or tried to protect) the workbook/project.

I'm totally baffled and I've messed around with several workbooks over the past year-and-a-half trying to get this to work. It's not possible this is still a bug Microsoft hasn't fixed in Excel 2007 or 2010 yet, is it? Any help or experience anyone has to offer would be greatly appreciated... I've been pulling my hair out for a long time.


We are embarking on a large program of project work that has 15 separate streams of activity. We need an excel based supply and demand model to assist with resource management. Can anyone supply a model for me to use? (example attached that does not give me the over arching view that I am looking for)


Hi,

I need to allocate a resource based on ranking of records in a spreadsheet:

Product Limit Rank Allocation
200-25 40 9
200-27 50 9
200-30 60 4 60
200-33 45 3 45
200-35 32 5 10
200-37 11 6
200-40 20 11
200-43 10 11
200-46 80 2 80
200-50 70 6
200-52 23 6
200-55 15 1 15
210

This is what I need it to look like - an allocation of 210 between the highest ranked products. The last one that gets the resource is product 200-35 (and it only gets 10 because there is no more left to allocate). How can I do this through formula?

TIA.




I am trying to have the calendar in the second tab of the attached excel file to auto populate based on info in the first tab.
In the first tab, there is the start and end dates of certain projects. The calendar will need to only show the project name of every project being worked on that specific day.
Some days will have more than one project being worked on, the Calendar will just need to list them all under that day. The Start and End dates count as days the project being worked on as well. We will also need the calendar to auto update whenever we modify the dates in the first tab or add/remove projects (rows).

Please let me know if there is a possible way to do this by formulas or are Macros the only solutions as I don't have any Macro experience.

Thanks all in advance.

Hi, I collect employee times in worksheets. A separate worksheet/workbook per employee. I require a macro to consolidate all of the data from all of the workbooks per employee. For example:

Employee: Fred (Cell A2) Date: 22/02/08 (Cell C2)

(Data starts in Row 11 - Col A - Col G)

Row 11 - Project /Activity Mon Tues Wed Thurs Friday Total
Row 12 - 1234 Project 1 7 7 7 7 0 28
Row 13 - 1236 Project 2 1 1 1 1 8 12
Row 14 - 23 Same, if person is working on more projects
Row 24 - Public Holiday/Annual Leave
Row 25 - Sick

Employee: Barney (Cell A2) Date: 22/02/08 (Cell C2)

(Data starts in Row 11 - Col A - Col G)

Row 11 - Project /Activity Mon Tues Wed Thurs Friday Total
Row 12 - 1234 Project 1 7 7 7 0 20
Row 13 - 1238 Project 3 1 1 1 8 12
Rows 14 - 23 Same, if person is working on more projects but may not be.
Row 24 - Public Holiday/Annual Leave
Row 25 - Sick 8 8


If I have say 20 employees, I would get 20 returns each month. What I need is a macro that will open all the files kept in C:\mydocuments\timesheets and consolidate the employee, date, project/activity and Total from all the sheets so I get a return that looks like:

Fred 22/02/08 1234 Project 1 28
Fred 22/02/08 1236 Project 2 12
Barney 22/02/08 1234 Project 1 20
Barney 22/02/08 1238 Project 3 12
Barney 22/02/08 Sick 8

Can anybody help me with this please. I'm a novice when it comes to Excel!

Thanks

Dave.


I've posted this question in another forum but with no luck so far. Here's to hoping someone here can help me work through this problem.

I'm trying to accomplish 4 different things with my spreadsheet.
The first two sheets in the workbook are job schedules (House A & House B). For each job, there are required tasks that have assigned dates, personnel and billing code.

The next sheet is the conflict report. I want this sheet to automatically return all conflicts with respect to personnel and/or billing codes. For instance, if a person is scheduled to work two or more tasks at the same time, I want to know it. I also want to know if a billing code is being used during the same time period. For the actual data output on this sheet, I would want to see the personnel and/or code along with the scheduled events that are causing the conflict.

The next three sheets are calendars. Each is a little different in terms of what I would like to see. For all three, I want to type in a date and have the calendar automatically update with the following 30 days (or however many will fit in the pre-determined size of the calendar).

For the project calendar, I want to see a rollup of all the jobs scheduled. Ideally, I would want it displayed as a color coded cell.

For the job calendar, I would like to have a drop down menu where I select which job I want to view (from all the available jobs - in the example it is House A and House B) and have the calendar display all the activities that are occuring during the time frame specified.

For the personnel calendar, I would like to have the option to select as many (or as few) personnel as I would like (from all available personnel) and have the calendar display all the days each individual is working. Again, this is probably best handled with color coding.

I realize this might not make much sense, so just let me know and I will hopefully clarify.


I have couple of VBA programming related question:
1) I have created spreadsheet with VBA macros and have put password protection on the VBA project. It runs fine and does what it is supposed to do but after I close the spreadsheet (the instance of Excel is still open) the VBA project associated with the spreadsheet doesn't go away. If I repoen that spreadsheet in the same instance I see 2 VBA projects with the same name. How do I get reed of VBA project when I close the spreadsheet that has the VBA project under question?
2) Sometimes for the same spreadsheet if I close the instance of Excel i.e. not just closing the spreadsheet, the VBA project password window pops up asking to enter password. How do I stop this from happening?


This is probably a simple question


But I was wondering how I would save my project or a module so that I could access them from any excel file I open. In other words how do I make my modules and and projects independent of the workbook so when the xls file is deleted or moved off site the project and modules can still be located.

Thanks

Here's a relatively tricky one to get your brain going.

I'm looking at putting together a simple database to look monitor tasks, resource usage, and have the ability to report back on the total resource usage on each day (i.e. to identify over use)... The other purpose of the database is that I'd like to be able to (easily) see the effect of delaying one item upon another task.

Multiple users can be assigned to the same task, and there is no measurement of a man-hour requirement on a task - just a start/end date.

In other words, I want something like what MS Project can do - but instead of looking at a discrete project I'm after something that can be applied on an ongoing basis (Which project is pretty dreadful at).

Project's 2000's own limitations, especially with the one-step-only undo don't give me much faith in the ability of the program to do what I want it to do; it isn't exactly friendly to multi-user environments either.

At the moment my table structure is roughly as follows

Table : Users
User ID (Autonumber)
User Name (Text)

Table : Tasks
Task ID (Autonumber)
Task Description (Text)
Start Date (Date)
End Date (Date)
% Complete (Number)
Status (Lookup)

Table : Status Type
Status Type (Complete, Started, Unstarted, Cancelled)

Table : Resource usage by Task
Task ID
User ID
Resource Usage

So as you can see, on paper at least, all the information that I'd need to be able to chart of the amount of each resource being used should be there.

Question is, how do I get from this raw information into a report or a chart format that looks something like right? I have experimented with pivot charts using Users down the left hand side, Sum of resource use in the data field, and dates as the series fields but the resulting chart doesn't "sum up" resource usage where the start/end dates are different. Can anyone suggest how I could approach this differently, or if there is a template already out there for something similar to what I'm after?

Googling for this kind of thing turns out an endless supply of shareware databases, and even full applications for this purpose, although neither of these is an option (costs are a no go, and installing unapproved software is a no go as well) so I'm hoping someone here has a better suggestion!

Thanks!


Greetings! I'll try and make this short and sweet! I need to conditional format a cell if number of total hours worked(Decimal) does NOT equal the total TIME worked (h:mm) ...

We have a time sheet where we fill in both Start and Stop TIME for the day and than a section to add number, in DECIMAL, hours worked on a project. At the bottom are the totals for the projects in DECIMAL and the total work hours based on start and stop TIMES

Start Work: 08:00 (8am)
Stop Work: 17:30 (5pm)
---------------------
Project 1: 3.5 (hours - decimal)
Project 2: 4 (hours - decimal)
Project 3: 2 (hours - decimal)
---------------------
Time Working: 9:30 HOURS (Formula: 17:30-08:00)
Total Project Time: 9.5 DECIMAL
---------------------

I need to compare the Project time with Working Time and highlight the WORKING TIME cells if they are not equal.

I cant change the formatting of the fields so I need to convert the numbers. What I did was this in a blank cell to verify it worked (where D29 is the DECIMAL number 9.5)
=TEXT(D29/24,"h:mm") ==> This worked; the cell value was shown as 9:30 (from 9.5)

I thought I had it solved so I setup conditional formatting as follows on the TIME WORKING cell:
Cell Value not equal to =TEXT(D29/24,"h:mm") --> Format cell with RED FILL

The way I read that is it should turn the cell RED only if the 9:30 did value did not equal the calculated and converted 9:30. Sadly the cell stays red no mater what I do!

I have beaten my head against wall here - any ideas???


I have an excel add-in of which I'd like to view the code, however when I go to view it rather than prompting me for a password it comes up with:
Project locked, Project is Unviewable

An internet search threw up a couple of suggestions:

1. If can occur when using excel 97 or an earlier version.
I'm using 03 so this is not an issue.

2. It's a shared workbook.
This is an add-in not a workbook and doesn't have the same problem. I've converted a shared workbook (in which the project was unviewable) to an add-in and the project became viewable again.

Has anyone encountered this before? I'm interested in being able to protect my own projects this way if possible.

Thanks


I am working on a project management database within Excel. What I would like is when a cell is populated in Column A, a new column is automatically inserted to the left of a cell titled "* ALL PROJECTS AND TASKS TO THE LEFT*. I also would like if any cells are populated under any column from B onward, to the left of the above cell " ALL PROJECTS AND TASKS TO THE LEFT*" to automatically insert a column with that name to the RIGHT of that cell.

For example:

AG: *ALL PROJECTS AND TASKS TO THE LEFT*
Column A2: Project1
Automatically Insert Column in AG
AH: *ALL PROJECTS AND TASKS TO THE LEFT*
Column A3: Project 2
Automatically Insert into AH
and so on....

and...

B2: Task 1
AJ: Task 1
B3: Task 2
AK: Task 2

Hopefully this makes sense... if not I'll be more than happy to clarify!


I have a timesheet which I want to Total. The Project Numbers are in Column
A, and the Days of the Month are in Columns B-M. The hours worked on each
project are reported in this form. All employees report their time in this
same Worksheet.

To Total this form, I want to do a VLookup down Column A for say ProjectA,
when I fnd it, I want to total the hours across that row. I may find
ProjectA multiple times in Column A (because of multiple employees using same
spreadsheet), and I want to continue to total the hours worked on Project A
all the way down.

Then, I want to continue doing VLookup for all projects. I have the project
list on a separate worksheet in the same file.

I have not used Visual Basic editor much at all, but I believe I want a
macro to do this. Could anyone please help?


Thanks,
Leann



Hi
I am looking for a template or software based on excel with gantt chart for personal task scheduling and personal time management such as MS project.Not very simple and not proffesional as project management software.

thanks


Hi,

I have been sent a file to work upon..... it has a macro in it which i want to look at....

but when i try to open the vba project of this file a pop-up appears saying "project is unviewable".... it does not asks for any password also.....

I contacted the person who sent it to me and he said he never put any password to the vba project of the file .... he is also unable to see the project...


can anyone please tell me why I am getting this.... and how to open the project in vba....
how can it become unviewable when the creator did not put any kind of lock or protection to it... and had it be done the project should have atleast asked me to provide any password to view the codes..... please help...



Regards,
Premanshu.


I have an excel sheet which has two columns- Employee ID and Number of hours worked on project. Employee IDs and Number of hours worked go like this-

ID Hours worked on project

101 .76
101 .33
102 .97
103 .77
103 .54
103 .65
103 .66
104 .87
104 .64

This goes on for 1830 ID values


I need a formula to calculate the total number of hours worked by each employee, for example Employee 101 has worked a total of 1.09 hours. Employee 102 has worked .97 hours. Employee 103 has worked 2.62 hours and so on for each employee. Each employee ID occurs multiple times (in many cases) because each employee works on several projects at the same time.

Please give me a formula, thank you very much for the help!


ALL,

I'm working on a VB6 project right now that has about 400K lines of code in it, so I'm trying to see if there's a way I can manipulate the IDE. e.g. - be able to write a small sub that runs through each module in the project and read the lines for CALL statements.

I already have something similar written in vba with excel, but obviously this project is NOT in vba. is there something similar to the extensiion library for vba, in vb6 or vb2010?? I have already found a package that does it in vb6 he http://www.vbforums.com/showthread.php?t=448824

However, I'd like to download an OLB or DLL for vb6 if there is one. All I want is access to the extensibility object structure for this VB6 project like what VBA does. all vba requires is a reference to the ext. DLL.

is it that simple in vb projects too??

thanks for any help guys!




Hello all,

New to the forum, so let me introduce myself - my name is Alexander - a Canadian expat living in Australia working for a resources company. I am fairly comfortable in Excel, however this has me stumped and I have made no headway with all my searches on Google.

I have been asked to auto-populate a calendar tab using data from a project management tab in the same spread sheet essentially to ensure we don't double book ourselves. We want the project title to show up in the calendar on the due date (not a date range) and to have it update itself from the project management tab where we will manage our workflow.

To explain:
We have a project that is due on 30 June 2013 and enter it into the project management tab; Said information shows up in the calendar tab on the due date without the need to manually enter the information again (because gosh knows project-based jobs always have a lot of movement for milestone and due dates); When a field in the calendar has data, it changes colour (conditional formatting, something I can figure out, but just running through everything); and If the due date has to update, we simply change it on the project management tab and that change is reflected in the calendar.

There are a pile of other things I have started in this spread sheet - but what I am focused on is pulling the Project Name from the one tab, to the calendar tab under the appropriate date.

Am I off my rocker to think that this is possible? Or is this something that may be possible?

I'd appreciate if you could let me know either way :-)

Thanks heaps!

// A

I have attached what I have done so far - basically it's pretty... and useless.

Planning.xlsx

Hi

I am needing a spreadsheet that calculates my penalty rates. Basically i want to be able to put in the hours worked for whichever day and it automatically calculates how much is time and half and double time.

For Example, if i worked on a monday the first 8 hours are normal time, the next 2 hours are time and half and any hours after that are double time. - This is consistent mon - fri

On saturdays when i work it is time and half for the first 2 hours then double time the rest of the day.

I want to be able to just put the total hours in for each day and it then just works out how much is normal time, time and half and double time, can anyone help me pleeeeease.


I can download files from SharePoint through VBA. But I can't seem to save the file back to SharePoint under a different name. I get a "Run-time error '1004': Method 'SaveAs' of Object '_Workbook' failed. Does anyone have any suggestions?
My line of code is:

HTML Code:

ActiveWorkbook.SaveAs Filename:= _
        "https://central.xxxx.com/corp/acctg/projects/Project Accounting/Chembio/Enzymes/Project Management/201011 Billing - 2121.xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False





I'm trying to do a resource allocation sheet. Basically I have months going across the top and an "x" in each cell for the months that the project is active. I want to count the "x"es and then multiply by the number of resources used (in a different column). Seems like it wouldnt be hard but i can't seem to figure it out. Thanks!