Guild of Project Controls: Compendium | Roles | Assessment | Certifications | Membership

Tips on using this forum..

(1) Explain your problem, don't simply post "This isn't working". What were you doing when you faced the problem? What have you tried to resolve - did you look for a solution using "Search" ? Has it happened just once or several times?

(2) It's also good to get feedback when a solution is found, return to the original post to explain how it was resolved so that more people can also use the results.

Excel-like features of MS Project

11 replies [Last post]
Evgeny Z.
User offline. Last seen 35 weeks 5 days ago. Offline
Joined: 13 Jan 2008
Posts: 442
Groups: None

Dear all,

I wonder whether MS Project (I use 2007) has the following Excel like feature:

I want that I would setup several parameters (cells) and then when I change them, then some other parameters of tasks, resources etc would change depending on the parameters of these cells, based on certain formula

E.g. Say I have a lot of resources, which have specific hourly rates, but these hourly rates are dependent on a few other parameters (e.g. currency, country perceived risk etc). So, if I change these few parameters, then hourly rates of all resources are also changed, based on specific formula.

Replies

Bogdan Leonte
User offline. Last seen 2 weeks 6 days ago. Offline
Joined: 18 Aug 2012
Posts: 284

Greetings,

In MS Project  2010 this can be done by right click-ing the respective column->custom fields, in the menu select formula, insert the formula of the resource hourly cost, which depends on your parameters. Let's take an example:

1. Create Another Number Column with the title Variable Wages (use the custom fields options to create the formula);

2. Create Columns for Parameters that influence the wages:

Parameter 1: Curency - Create a Number Column and enter the name of the colmun and the value of the currency (euro/dolar = 1.23)

Parameter 2: Inside/Outside the Country - Create a Text Column and enter the previous mention Parameters Inside/Outside

Parameter 3: Risks (Let's consider Weather Conditions) - Fine Weather, Rain, Snow

3. Insert Formula in step 1:

= if (parameter 2 = option 1 if (parameter 3 = option 2 -> parameter 1 *  standar rate * factor 1 * factor 2) else....else...)

and so on.

You might want to use 2 columns for the rates so that you don't end up with a loop.

What you want can be done but it is a little tricky since MS Project.

Hope I was fearly clear about my ideea

Best Regards,

Bogdan

Louise Katherine
User offline. Last seen 9 years 51 weeks ago. Offline
Joined: 4 Mar 2013
Posts: 3
Groups: None

Microsoft Programmer is a externalize management software programmer formulated and sold by Microsoft which is premeditated to assistance a send manager in processing a organization distribution resources to tasks chase procession managing the budget and analyzing workloads 70-668 Dumps

sam naz
User offline. Last seen 11 years 24 weeks ago. Offline
Joined: 2 Oct 2012
Posts: 2
Groups: None

the expertise to plan well forward in enhance, by bearing in mind the continuous changes that are occurring in the business surroundings. With the help of the training, abilities and knowledge offered http://www.certificationkey.com/HP-Certification-Training/HP0-J49.php

Evgeny,

in Spider Project you can create formulas that link not only fields but also cells, or fields with cells.

And I expect that you will not need formulas for your task. Spider cost components may include expenses in different currencies with different exchange rates that are entered in Cost Components table and you will get total cost selecting what currency to use for total cost (and change this selection at any moment). Exchange rate (cost component unit cost) may be changed in this table and everything will be recalculated.

Since Spider keeps archives you will have a history of exchange rates changes.

If you manage many projects all exchange rates may be set and changed in Cost Components reference-book. In this case all projects that are linked with this reference-book will use the same exchange rates and you can change them in only one place.

Evgeny Z.
User offline. Last seen 35 weeks 5 days ago. Offline
Joined: 13 Jan 2008
Posts: 442
Groups: None

Vladimir,

do you have in Spider customizable Project - level taks (e.g. USD/Euro echange rate etc), which other field could fere to in formular? (This is opposite to task-level custom fields, which one has in Ms Project). 

Evgeny,

in Spider Project you would just create a formula.

Amazing that such easy things are so complicated.

Evgeny Z.
User offline. Last seen 35 weeks 5 days ago. Offline
Joined: 13 Jan 2008
Posts: 442
Groups: None

I just found one way to do this: By making a link from MS Project to Excel file.

 

Edit=>Paste Special = Paste link.

The disadvantage is that I have to maintain this link betweem Ms Project and Excel.

Evgeny Z.
User offline. Last seen 35 weeks 5 days ago. Offline
Joined: 13 Jan 2008
Posts: 442
Groups: None

 

This would require Project-level custom fields. E.g,  I would have single project-level exchange USD/Euro exchange rate field, and 20 resources, whose hourly rate depends on this single custom field.

However in my MS Project project-level custom fields are grayed out

AMAR SOOGRIM
User offline. Last seen 11 years 43 weeks ago. Offline
Joined: 20 Dec 2011
Posts: 8
Groups: None

check in the custom feilds. you should be able to do it there

Evgeny Z.
User offline. Last seen 35 weeks 5 days ago. Offline
Joined: 13 Jan 2008
Posts: 442
Groups: None

Sorry for coming back on this.

Is there really nothing like this, which MS Project has?