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.

Wednesday, July 22, 2009

New Lines within Excel Cells

In MS Office, (Word for example), you can use the Shift + Enter to create a new line without breaking the paragraph or a cell in a table. In Excel this doesn't work as it takes you to the next cell.

To Achieve this goal in Excel:
  1. Type something within a cell to the point where you need a line break.
  2. Without exiting the cell, type: Alt + Enter.
    This will position the mouse in the next line within the cell.

Tuesday, July 14, 2009

Update SharePoint Lists from Excel 2007

Unlike Excel 2003, the 2007 version doesn’t let you update a list in Excel and then synchronize it with its linked list in SharePoint.

Microsoft developed a work around which requires the installation of an Excel add-in.

Comments: The document should be saved as XLS file rather than in 2007 format: XLSX.
You can read the full document in MSDN:
  1. Download Excel 2007 Add-in: Synchronizing Tables with SharePoint Lists.
  2. In Excel, Click the Microsoft Office Button, click Excel Options, and then click the Add-Ins tab.
  3. Select Excel Add-Ins in the Manage drop-down list and click Go.
  4. In the Add-Ins dialogue box, click Browse, navigate to the Excel 2007 SharePoint List Synchronizing Add-ins, and then click OK.
  5. In the Add-Ins dialogue box, verify that the SynchronizeWSSandExcel option is selected and then click OK.

The Add-Ins dialogue with the Rwsplist option selected

Right click any item in a SharePoint list in Excel.You will notice that the Synchronization option is now available as in Excel 2003.

Sunday, July 12, 2009

Using Excel in Multi-User Applications (or Excel - SharePoint Collaboration)

Excel is a great tool when it comes to a one-man-band show. However, when you need to collaborate with others, especially when the source data is continuously changed or provided by others, there are versions and synchronization issues that require extra care.

Many Excel advanced users are not aware that one of the main objectives of SharePoint from Microsoft is to address exactly these issues and does it extremely well for Excel.

For example, you can post the latest version of an Excel document to a SharePoint library with an automated email sent to interested parties who will continue working on the document with 'Check-in, Check-out' capabilities and post it back. Thus you eliminate the 'last version' issue.

But there are even more powerful options. Let users enter data in a SharePoint web form and easily pull the data into your Excel as the basis for Pivot Tables, Charts, or models you developed.

SharePoint is pretty much known to most companies. Its base product, WSS 3.0, comes free with Windows 2003 Server or later. It is likely that your company already uses it and your IT team will be happy to create a sub-site for your needs.

In future posts, I will demonstrate how Excel works with SharePoint in a multi-user application.

Saturday, July 11, 2009

Excel Case Sensitive Lookup

In Excel, the various lookup functions are not case sensitive so you can't differentiate between Joe and joe. Once the lookup locates the first match it references to that occurrence. There is a work around to locate the exact case sensitive match.

Using a Conditional test and the Exact function can differentiate between “Joe” or “joe” as explained in Microsoft support case:

However, it will always test the first “Joe” available so you won’t be able to get to the next one.
Therefore, you will need to use the Match function and calculate a narrower range to search where the first insensitive case match has been excluded.

This solution applies to two identical insensitive rows, so if you expect more than that you will need to add more nested ifs to further narrow down the lookup ranges. You also need to specifically address the last row in the range or add a fictitious entry that has no match.

The solution has been tested on 2003 and 2007 versions.

In I1:J4 enter the lookup table

aB Abraham
Ab Aaron
Cd Daniel
Rs Kevin

In A1:C1 enter the titles:
Key Name Range
In A2 A6 enter the lookup values:
Enter the following formulas into B2:B6 and C2:C6
  • For clarity purposes, the Range Column C2:C6 shows the calculated range based on whether there is no match at all, there is a perfect match, or a narrower range is needed for an imperfect match. The formula is:
  • The Name column uses the Indirect function on the calculated range to lookup the correct range to get the name.

The result after applying the formulas:
Key Name Range
cd Missing I4:J$4
Ab Aaron I2:J$4
aB Abraham I1:J$3
Cd Daniel I3:J$3
Ed Missing Missing
Of course, you can get rid of column C and incorporate its formula within B.

Welcome Message

This blog deals with Excel and Excel collaboration with SharePoint. I post SharePoint dedicated topics on Raytech's blog: