View Khurram Jamshed's profile on linkedin

Sunday, June 12, 2011

Project Server 2010 Collaboration - Sync of excel data with List (both directions)

One of the 'great' advantages of having Enterprise Project Management solution in-place is to have effective ways of information sharing between the Project team members. Being developed on top of the sharepoint architecture, it already inherits all the sharepoint features and options to leverage Project team members to help them collaborate in better ways.

Often i came across to the clients ultimate wish out of the solution is to have impressive/color-ful/state-of-the-art reporting to please the higher-ups. And what bothers me more is the kool after-thought response of the users related to the adaptability planning of the solution. Because a Project Management solution is a cultural change, at least I think like this, and the project to roll-out such cultural change can never be a success unless the adaptability plan would be ready upfront.

I usually present Enterprise Project Management Solution to my clients/users/customers divided as 4 pillars. And one of the most overlooked pillar, in my experience, is the Collaboration part of the solution.

I will touch the Collaboration part a little in this post, since its a huge domain to cover, in terms of features the product offers, the usability etc. etc. I have always been a vocal supporter of the use of excel sheet, for various stuff, in parallel while managing Projects. My focus in this post would be on how effectively the whole office suite can work together to enhance the project teams information sharing effort. And to be more precise about the post topic, I would explain how to create and sycn excel spreadsheet with SharePoint site in both ways, i.e. from spreadsheet to SharePoint list and from SharePoint list to excel spreadsheet.

In excel 2010 the capability to synchronize spreadsheet with sharepoint list is not available, yes unfortunately this is the fact of life :),  so we have to go little over the top to cover this deficiency of the excel 2010.
  • Download and install: Excel 2007 add-in: Synchronizing tables with Sharepoint lists.

  • Create or open excel spreadsheet, make sure if you open a saved file it must be .xls
  • Create an excel table

  • from the ribbon menu, select Design tab, and click Publish and allow sync

  • enter the sharepoint site name

  • click publish, and the table will be published on the sharepoint list with all the data you have just feed-in.

I can hear some WOWs .... :), but this is not it. you can also change the data of the same list and sync it back with your spreadsheet.
  • change some data in the list, eg. insert few more rows

  • go back to the spreadsheet, rite click on the table, select table option and click on synchronize with SharePoint.

and there you go, your data is synchronized in both directions. 

Office suite together with Project Server can help enterprise to execute the business smoothly and effectively. There could be many scenarios where the above solution can help the Project team save time and effort.

Happy synchronizing, and as usual you value-able feedback is always welcome.


Post a Comment

Spammers, don't waste your time as the comments will only appear after moderation.