Using Excel in Multi-User Applications (SharePoint)

The article was first published on http://excel.shilor.org. You can read the article with its associated images in: http://excel.shilor.org/2009/07/using-excel-in-multi-user-applications.html

In a previous post I suggested using Excel in collaboration with SharePoint. I will demonstrate this concept in the following simple scenario.

For this solution your organization should have WSS 3.0 or MOSS. It is assumed that you know something about Excel Pivot Tables, Names, and Charts.
Let's assume that you work in a multi-plant manufacturing environment. Plants assign people to track their daily waste data in a SharePoint list. The list may have the following columns:

• Plant (Lansing, Detroit)
• Machine-Type (Printer-A, Printer-B)
• Date
• Waste-Lb

Any SharePoint List lets you export its data to Excel (2003 or later).

1. Navigate to the list
2. In Actions, select Export to Spreadsheet.
3. In the Pop up window click Open.
   -For Excel 2007 click Enable in the Security Notice window.
   -You may need to enter your user and password which should have access to the list.
4. The list will open in your default Excel program.
   -from here you can name the list and refer to it from a Pivot Table, Chart, or any other model.
5. To refresh the list with the up-to-date data, just select any List's cell and click the 'Refresh' icon in Excel 2007 or right-click the cell, select List / Discard changes and Refresh List.

Anytime you refresh the list, the name you assigned will be correctly adjusted to the new range. Upon refreshing the Pivot Table for example, you will view the new numbers.