Her request led to creation of the Forms Portal, a SharePoint site designed to replace the Excel spreadsheet and facilitate access to its valuable data to the whole company.

We needed a SharePoint Dashboard that the end user could select filter criteria once and it would be pushed to all pivot tables on multiple sheets within one workbook

spreadsheets are the definitive killer app for personal computers and Excel is a great spreadsheet. It’s just that using Excel to keep a list is a bad idea.

You have now connected a SharePoint filter to a single workbook that contains multiple worksheets with multiple pivot tables and are driving all of the pivot table filters with one single filter from the SharePoint Dashboard.

Most organizations have been using Excel to do analysis, create charts and design dashboards for years, yet there is a lot of functionality in Excel that is not easily accessed in SharePoint, sometimes even for those with Excel Services. The question becomes, how does one translate their work from Excel to SharePoint? This means more than linking to an Excel workbook in a Document Library; rather, it requires displaying the charts and dashboards directly within SharePoint.

Well, for Office 2010, Microsoft has essentially brought that concept to uploading as well. They have created a new applet called the “Office Synchronization Center”. When you tell Word (for example) to save a file, rather than sending it directly to the SharePoint site, it hands it off to the Synchronization Center, which does the uploading, including such niceties as retrying if for some reason the upload fails the first time. It also allows you to continue working once you have started the save process – sort of like a “background save” on steroids.

With Mark Miller as the host, Laura Rogers will demonstrate just how flexible and useful the Out-of-the-Box Web Parts are in MOSS. She will take a detailed look at the Filter, KPI, Excel Web Access, Outlook Web Access, Business Data Catalog, and Data View Web Parts. She will then look at how to customize some of these web parts using SharePoint Designer while still adhering to the Industry Best Practices. All of this is done without using any code. This session in itself is a best practice, because it is best to learn what SharePoint can do out-of-the-box, before writing any code.

In this live online, hands-on workshop, Laura Rogers and Mark Miller will show you techniques that might not be so obvious when first starting to use SharePoint with Excel.

Hopefully all of this wasn’t too confusing – it’s a hard formula to decipher, but as with all formulas, the best approach is to break it into chunks, determine what each chunk does independently, then merge them all back together and run through the entire thing with a series of test data.

I wanted to start off the series on the “Date and Time” set of functions by examining a specific problem that many folks have posted questions about – that is, how do you calculate the difference (number of days) between two dates, while excluding weekends? (Iain on the STP forums asked this recently, which sparked my interest in writing this)