Monday, July 27, 2009

Using Excel in Multi-User Applications - II

In a previous post I suggested the option of using Excel together with SharePoint. I will demonstrate this in a simple scenario, probably known to SharePoint users.
For this solution your organization should have the free WSS 3.0 version (as a part of Server 2003) or the non-free product MOSS (Microsoft Office SharePoint Server 2007).
Let's assume that you work in a multi-plant manufacturing environment. The plants assign people to track their daily waste data in a SharePoint list while you want to create a Pivot Table or chart to show patterns.
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 permissions 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.
Any time you refresh the list, the name you assigned will be correctly adjusted to the new range. Upon refreshing the a Pivot Table for example, you will view the new numbers.

No comments:

Post a Comment

Enter your comment here