Link excel cells to MS Project Resource Usage

[alebertoni85]

I have this problem. I want to link an excel file and a project file. I'd
like to copy the value of one cell in the excel file to the 'actual work'
cell of a task in the resource usage sheet in Ms project. I'd like to copy
also the link between the cells so that updating the value in the excel file
i can see it updated also in the resource usage sheet. Is this possible?
thank you
Alessandro

[Raine]

The essential trick to allow selection of Linking is detailed in Exporting to Excel. However, let me warn you .These simple linkages are damaged and can be lost. you must keep both files in the same folder on your disk and never move them.

for more useful Project information please visit this web address: http://project.mvps.org/faqs.htm

[Effren]

You can add links to work items in Microsoft Project or Microsoft Excel, by using the following procedures.

Required Permissions

To perform these procedures, you must be a member of the Contributors group or have the View work items in this node and Edit work items in this node permissions set to Allow. For more information, see Team Foundation Server Permissions.

To add a link in Microsoft Excel

1. Open the work item list in Microsoft Excel.
   
2. Add the Links and Attachments column to the file.
   
3. For more information, see How to: Add or Remove Columns in the Work Item List.
   
4. Select the cell under the Links and Attachments column for the work item to which you want to add a link.
   
5. On the Team tab, in the Work Items group, click Links & Attachments. Note:
   
6. In Microsoft Office Excel 2003, use the Team menu.
   
7. In the View/Edit Work Item Links and Attachments dialog box, select the Links tab.
   
8. Click Add.
   
9. In the Add Link dialog box, under Link Type, select the type of link you want to add.
   
10. Under Link Details, type the requested information for the link type you selected.
    
11. For more information about the different link types, see Working with Links and Attachments in Work Items.
    
12. Under Comment, enter any comment that is important about this link.
    
13. Click OK and then click Save.
    
14. If you are prompted to confirm saving the link to the server, click Yes.
    
15. Click Close.

To add a link in Microsoft Project

1. Open a Project Plan in Microsoft Project.
   
2. Select a cell in the row of the work item for which you want to add a link.
   
3. On the Team menu, click Links & Attachments.
   
4. In the View/Edit Work Item Links and Attachments dialog box, select the Links tab.
   
5. Click Add.
   
6. In the Add Link dialog box, under Link Type, select the type of link you want to add.
   
7. Under Link Details, type the requested information for the link type you selected.
   
8. For more information about the different link types, see Working with Links and Attachments in Work Items.
   
9. Under Comment, enter any comment that is important about this link.
   
10. Click OK and then click Save.
    
11. If you are prompted to confirm saving the link to the server, click Yes.
    
12. Click Close.

hope it helps you

[robodude]

have a look at the few links below for more infromation :

How to: Create a Microsoft Excel or Microsoft Project File from a Work Item Query

How to: Connect to Team Foundation from Microsoft Excel or Microsoft Project

How to: Add Work Items in Microsoft Excel or Microsoft Project

How to: Import Work Items in Microsoft Excel or Microsoft Project

Managing Links and Attachments in Microsoft Excel and Microsoft Project

[alebertoni85]

I've realised that I don't have a team menu in my copy of MS project. How is it possible? I'm using MS project 2003, do you thing is something related to an old version of the software or maybe something related to the licence or maybe something else?

thanks
Alessandro

[Calgary]

I have this problem. I want to link an excel file and a project file. I'd
like to copy the value of one cell in the excel file to the 'actual work'
cell of a task in the resource usage sheet in Ms project. I'd like to copy
also the link between the cells so that updating the value in the excel file
i can see it updated also in the resource usage sheet. Is this possible?
thank you
Alessandro

Good day I am a project contorls specialist in the oil biz ad there is no way that i am aware of to do this, you need to create a specific program that will enable the two programs to communicate, for example there is a progress report due on a daily basis, there is only a few people that have access to MS project but everyone has access to excel, the progrress is fillled out in excel,, the only way to import the data is by copy and paste, what if ther is a way that when the person using excel could just type the value in to a cell that would automatically update the ms project schedule per each activity is there was a way to do that ???

[chroma]

A simple way to get where you want to go, is to create a series of CHOOSE functions, each with 12 arguments and one input. With Function AutoComplete, you can quickly write the proper formula syntax. The input for all the CHOOSE functions is the MONTH number. The argument list for each CHOOSE is the values of the 12 months to which that CHOOSE refers. From easily detecting the functions that you want to use to getting help completing the formula arguments, you will be able to get formulas right the first time and every time. You'll have a CHOOSE for each of your spending categories, and another CHOOSE for each of the same categories in the BUDGET table.For that you need to create a table structure with columns like Activity, start and end day, day 1, 2,3, etc. After that whenever a day falls between start and end day for a corresponding activity, you need to highlight that row. For that you need to identify whether a day falls between start and end. We can do that with the below formulas,=IF(AND(F$8>=$D9, F$8<=$E9),"1",""). Which means, whenever, the day number represented on the top row is between start and end we will in 1 in the corresponding cell. After that whenever the cell value is 1, you will just need to fill the cell with a favorite color and change the font to same color, so that you don't see anything but a highlighted cell, better still, whenever you change the start or end dates, the color will change automatically. That's it and your job is done.

[Calgary]

A simple way to get where you want to go, is to create a series of CHOOSE functions, each with 12 arguments and one input. With Function AutoComplete, you can quickly write the proper formula syntax. The input for all the CHOOSE functions is the MONTH number. The argument list for each CHOOSE is the values of the 12 months to which that CHOOSE refers. From easily detecting the functions that you want to use to getting help completing the formula arguments, you will be able to get formulas right the first time and every time. You'll have a CHOOSE for each of your spending categories, and another CHOOSE for each of the same categories in the BUDGET table.For that you need to create a table structure with columns like Activity, start and end day, day 1, 2,3, etc. After that whenever a day falls between start and end day for a corresponding activity, you need to highlight that row. For that you need to identify whether a day falls between start and end. We can do that with the below formulas,=IF(AND(F$8>=$D9, F$8<=$E9),"1",""). Which means, whenever, the day number represented on the top row is between start and end we will in 1 in the corresponding cell. After that whenever the cell value is 1, you will just need to fill the cell with a favorite color and change the font to same color, so that you don't see anything but a highlighted cell, better still, whenever you change the start or end dates, the color will change automatically. That's it and your job is done.

Thank you Chroma for the reply,


I agree with you the CHOOSE function is a very powerfull tool within excel but will not achieve a live link situation with MS project, that when the excel sheet is updated the MS project file is as well, like linking 2 excel workbooks together. I like your formulae in the above email very accurate and good description of that argument.




Calgary