Working in Project and Excel Simultaneously RRS feed

  • Question

  • Hello,

    I have been searching all over the webs for this answer, and i am lead down many rabbit holes.

    I have persons A, B, C and D working on their own separate MS Projects, which automatically updates and "Master" Project file. Now, I want Projects A, B, C and D to dynamically/automatically update an excel spreadsheet for Person E. I tried linking the cells through copy and paste but it doesnt get the job that i need done. Can anyone point me in the right direction. Thank you in advance.

    Monday, August 26, 2019 4:37 PM

All replies

  • EAGFD19,

    If you are working with a dynamic master, which is what it sounds like when you refer to "automatic updates", then you positively absolutely do NOT want to link that file to Excel. Dynamic structures (i.e. master/subprojects) in Project are prone to corruption. None of the files in the structure should ever be renamed, overwritten, moved, or "saved off" to another location.

    What exactly is the purpose of updating the file for person E? Understanding what you are trying to accomplish will go a long way in helping you get what you need.


    Monday, August 26, 2019 7:38 PM
  • Person's A-D are working on individual projects that track event dates, equipment bought, and tracking their budgets. Person E is the finance person who only needs to track the spending for equipment and travel in a "Checkbook". When Person's A-D input into project what they spent on travel or equipment, the amounts are then sent to Person E, who only cares about money and what it was spent on.

    As for the "Master", there is the boss, who would like to see a compiled version of person's A-D. He doesnt need to see the checkbook, he wants to have a "1-stop-shop" for whatever info he needs for all projects.

    Hope this helps. Thanks

    Tuesday, August 27, 2019 1:28 PM
  • btw, I am also open to programs other than project or excel
    Tuesday, August 27, 2019 1:28 PM
  • EAFD19,

    There are various ways to transfer date from Project to Excel. If your finance person is able to work with pivot tables in Excel, then you could export Project data to Excel using Project's Visual Reports feature found under Project > Reports group > Visual Reports. If that doesn't work, you can always set up a custom view in Project that focuses on the cost and use copy and paste to Excel.

    My preference is to use VBA to export Project data to Excel. I can help you set that up if you'd like.

    As I noted in my original response, working with dynamic masters is courting corruption. And from your description of what your boss wants to see, you could use a static master instead of a dynamic master. A static master is simply a snapshot in time of all subprojects in a single file. It is not prone to corruption since there is no link structure. However, it does need to be re-created each time your boss wants to see an update, but that is easily automated by recording a macro of you creating a static master one time. Thereafter, you (or the boss) simply runs the recorded macro. A static master is created by un-checking the "link to project" option in the lower right part of the Insert Project window.

    With regard to your other comment about being open to other approaches, I'll leave that to anyone else who may jump into this thread. However, it doesn't sound like you are really using Project as a scheduling tool, you might want to consider doing everything in Excel, then all these issues go away.

    Hope this helps.


    Tuesday, August 27, 2019 4:08 PM
  • i am open to leaving project and doing everything in excel. The reason why we used project is because they like the Gnatt chart feature. So with what i want to do then, it can all be accomplished in excel? Because the reason i want to do all of this is because each project lead has 2-3 different excels they have to update, and i am trying to get it down to 1, and the boss and finance person can see only info they care about.
    Tuesday, August 27, 2019 4:21 PM
  • EAFD19,

    If the Gantt display is important then you still need Project, but if you can live without the Gantt, then yes, I think you are much better off keeping everything in Excel.

    You mention that each lead has multiple Excel Workbooks/Worksheets they update, I'd look at ways to combine the data unto one Workbook/Worksheet. Personally I think it is much easier to customize reporting in Excel than in Project. In fact, when I need custom reports for Project, I export Project data to Excel using VBA.


    Tuesday, August 27, 2019 8:00 PM