During a recent class one student asked if it is possible, using the user interface in MOSS, to publish and lock down separate columns (or only show certain columns) from an Excel 2003 spreadsheet while still having the ability to update each published list from the single, source Excel worksheet. Basically, the scenario is - the company is running MOSS but is still running Office 2003 and do not envisage upgrading to Office 2007 in the near future.
So one possible solution I proposed was to selectively publish columns in the Excel 2003 spreadsheet and then lock down permissions on the published lists in SharePoint.
In Excel 2003, it is possible to publish and maintain a two-way synch with a SharePoint list. Here's how:-
- In the Excel 2003 worksheet, select / highlight the range of columns you wish to publish
- Right-click the selected range of cells and, from the contextual drop-down menu, click Creat List...
- Next, right-click within the outlined section of cells and in the contextual drop-down menu click List > Publish List...
- In the Publish List to SharePoint Site dialogue, add the URL to the site where you want to publish the Excel 'list' and provide a name for the new list. Important - check the Link to the new SharePoint list checkbox, as shown below.
- Click Next to confirm the columns within the Excel 2003 spreadsheet that are being published to the new SharePoint list
- Click Finish
- Proceed to select and publish additional column-sets within the same Excel spreadsheet
Each set of columns published becomes a separate SharePoint list. Changes made to the SharePoint list can be synched back to the source Excel 2003 spreadsheet. Changes made to the source Excel 2003 spreadsheet can be synched to the respective list on the SharePoint server, as shown below.
However, remember that this two-way synch is not available in Excel 2007 and that any formulas within the source Excel 2003 worksheet will be converted to values in the published version. The person updating the source Excel 2003 worksheet will need access to published SharePoint lists to successfully synchronize and push updates.