Export SharePoint 2007 List with Multi Selection Lookup Column into Excel

Whether you use MOSS or WSS 3.0 lists that include multi-selection items, you may find it hard to manipulate the column's items in Excel. For example, if you try filtering by a specific value, any cell with multi item values won't show because it is not identical to the filter value. Excel 2007 includes all the multi selection values in the same cell and treats all the text inside the cell as a single long string. Another issue is that the ;#n; (where n denotes the item-ID) shows next to the lookup.
There is a solution, though not a perfect one, for this issue. You can add to the right side of the Excel table as many columns as the # of values in the dropdown list, and apply formulas to strip one value at a time into a column. Then you can filter the table by the calculated columns as you do for any other table's columns.
This would nicely solve 10-30 list items but any time you have a new item in the drop down list you will have to add a column for that item.

Here is an example using Excel 2007 though it should work for 2003 as well:

The are two tables (lists) exported: the main one and the dropdown.
The exported list had the project column with multi-item selection. Each selection has a value and next to it its ID separated by a semicolon. One of the values is 'Estimating; #1.'
In a new column named Proj-1 you can see all rows that have the value 'Estimating.' You will have to create a separate column for each separate value as explained below.

 

The Project column is associated with the ProjectList dropdown list: (this is an excerpt from the entire list).



For each entry in the selection list (ProjectList) you prepare a column (proj-1, proj-2 etc.) and enter the formula below:

=IF(RIGHT(Source!$B2,3)=";#1",VLOOKUP(1,ProjectList!$A$1:$D$19,2,FALSE),IF(ISERROR(SEARCH(";#1;",Source!$B2)),"",VLOOKUP(1,ProjectList!$A$1:$D$19,2,FALSE)))

 The formula first tests whether that last item in the string is our item (which is ;#1 for Estimating). if not it searches for the string ;#1;. upon failure the cell will stay empty otherwise it will contain 'estimating.'

although estimating can be obtained directly from the cell or entered as a string, I found it easier to export the list and vlookup it.

Of course, for the next value you should test #2 and the lookup will be for 2. When testing for IDs greater than 9 you will need to use RIGHT(Source!$B2,4) as you need to test 4 characters.

To improve the process you can use the row() function to further automate the formula but I didn't want to add another complexity.

Web Part Page Error prevents opening it for update

When an error occurs after editing a web part page, for example by adding a custom WP, sometimes you can't access the page anymore and therefore you can't to fix the problem by removing that WP.

Although not well documented, there is a solution to this issue. You just need to add the suffix ?contents=1 to the page's URL. This will present the maintenance area of that page. In the maintenance page you can exclude the WP and view the page again.

For example, if your URL is www.myDomain/mySubSite/myPage.aspx, it should look like:
www.myDomain/mySubSite/myPage.aspx?contents=1.

Access violation error messages on SharePoint Lists

You configure some Lists or Surveys whose Item-level Permissions of Read access setting is set to "Only their own." However, when you run a crawl for these Lists or Surveys, you receive access violation error messages.

Here you can find the hotfix:

WSS 3.0:
Description of the Windows SharePoint Services 3.0 Cumulative Update Server Hotfix Package (Sts-x-none.msp): October 27, 2009 http://support.microsoft.com/kb/975002/


MOSS:
Description of the SharePoint Server 2007 Cumulative Update Server Hotfix Package (Coreserver-x-none.msp): October 27, 2009 http://support.microsoft.com/kb/975731/

Workflows are carried over upon saving SharePoint lists as templates

If you save a list or library as a template, the attached workflows are carried over. If this is not what you need you should detach the workflow before saving it as a template and re-attach it afterwards.

You need to be aware that newly created lists from such a template may not work correctly if created in another subsite, especially when they deal with editing or looking up items in lists that are now in another subsite.

It is sometimes a pain in the neck to get rid of such a workflow after the fact so the safest way to work is to store the list as a clean template in the first place.

Office and SharePoint Metadata Synchronization (2007 and 2010)

This post is an extension to a previous one adding the sequence for Excel 2010.

When uploading Office documents to a SharePoint Library the document properties are mapped to the Library columns by name. Therefore, you can easily add columns to the Library as appropriate to handle the MS-Office document properties. However, if you need an extra property for the document to map from a SharePoint column it is a bit harder. In Office 2003 it was easier but in 2007 it is more confusing:

1. Click the Office Button on the top left of the Office 2007 program (Excel, for example)
2. Select Prepare / Properties to show the doc's current properties.
3. Click Document Properties dropdown on the top left and Advanced Properties.
4. Select the Custom tab.
5. Here you can add a Name of a new property and enter a value for that property.
6. Click Add and OK and you are all set.

If the name matches a SharePoint Library column the property will pass upon uploading or downloading the document.

In Excel 2010:

1. Click File and select Info.
   You will (hardly) see the Properties section on the right.
2. Click to open the Properties dropdown.
3. Select Advanced Properties to open that window.
   This is the same window as in step 3 above.
4. Follow steps 4-6 above.

As Niall commented, this works as expected only upon upload. Afterwards when you update the doc's properties you need to do it twice, for the doc's and for the server's property. Otherwise the values won't be identical.

Adding Mark's important tip from September 7th 2012:
One can sync a cell value with a SharePoint list column via an Named Range given to the cell. See Mark's comment below.

How to limit a SharePoint user to view his list items only.

This is a simple procedure unless one needs to allow certain users to view all items, not only their own.

• To limit the view on a list click Settings / List Settings / Advanced Settings.
• For Read access and Edit access select 'only their own' as appropriate.

If you need to let certain users view and edit all items you need to provide them with Designer or Owner permissions.
Comment: Document Libraries don't have this option so you may need to look for a related feature on CodePlex http://www.codeplex.com/ (http://sharepointworkflows.codeplex.com/)

How to Lookup two or more source columns in SharePoint

Let's say you have a contact list that contains the columns: Fname, Lname and Phone#. In another list you would like to lookup this list by the combination of Lname and Fname and a space between them to get the contact's Phone#.

Add to the source list a calculated column of type text with the formula:
=Fname&" "&Lname.
Now you can create the Lookup Column in the target list pointing to the newly created 'FullName' column.

Remove Disabled AD Accounts from MOSS

You would think that whenever an AD account's attributes are changed, the new info will be synchronized with MOSS profiles - but this is not the case. The following walkthrough explains how to remove the AD disabled accounts from MOSS.
The first step is to change the LDAP OOTB search filter.
To get this done:

1. In Central Administration click on SharedServices1 on the left navigation bar.
2. Click User Profiles and Properties (under User Profiles and My Sites section).
3. Click View Import Connections.
4. Hover over the only connection you have, click the down arrow and then click Edit.
5. Scroll down to the Search Settings section. There is a box titled User Filter in that section. Paste in the following filter string and OK:
   Instead of:
   (&(objectCategory=person)(objectClass=user))
   Place this string:
   (&(objectCategory=person)(objectClass=user)( !(userAccountControl:1.2.840.113556.1.4.803:=2)))
6. In your navigation breadcrumbs near the top of the page, click on User Profile and Properties to navigate back there.
7. Near the bottom, click the bulleted link Start Full Import.
   This process will go through your existing profiles and mark any previous disabled accounts as inactive profiles that will no longer show or be accessible in SharePoint. Disabled accounts not previously imported by MOSS will be ignored and no profile will be created.
   
   The procedure above was taken from Mark Eichenberger's SharePoint Blog with minor editing:http://sharepoint.microsoft.com/blogs/MarkE/Lists/Posts/Post.aspx?List=fd220abd%2D850c%2D44a4%2D85d8%2D805efe79f85a&ID=10

Next, remove current extra accounts: In Shared Services1 / Profiles and Properties / View User Profiles select the Profiles Missing from Import and delete them.

Import the profiles again
Create index to the related Data Source (usually all items)
And lastly, update the Search Scopes.

You should now have  a clean SharePoint User Profile list synchronized with AD.

Cannot get the list schema column property from the SharePoint list

When you try to export a SharePoint list to Excel you may see this message:

This may happen because you have calculated columns in the selected view. I am not sure why, but some of the calculated columns can't be exported to a spreadsheet.

If you remove these columns from the view you will be able to successfully complete the export operation.

Permissions on a SharePoint List Column

SharePoint OOTB doesn't let you apply permissions on a column level. However, if you need to hide the column's content and it is a lookup field, you just remove the user's read permissions from the target lookup list. The user will see an empty column.
Although this is fairly simple and straight forward, we sometimes forget such simple tips,so I decided to post it.

Incorrect search results when using the This Site or This List options

There are several reasons for a search to bring wrong results in MOSS. Here is one incident we have experienced lately:
The Web application is named with the internal server name and so is the Search in Content Sources which is configured to use the address http://servername/.
There is a translation in the DNS server to http://www.myinternetaddress.com/ which lands in the same place. If you change the Content Sources definition to the DNS name you may affect the Search Scopes, which in turn will bring wrong results.
The remedy is to change the address back to the original one.

Calculate SharePoint Date Columns Excluding Weekends

Sometimes you may need to calculate a SharePoint date column by adding x work days to another date column.
Let's assume you would like to add 3 days since the list item was created to the target date column 'Due Date: Initial Review.'
SharePoint uses Excel type functions making it is easy to add 3 days to date columns but you also need 2 days to compensate for Thursday, Friday, or Saturday, and 1 day if the item was created on Sunday.

• Created + 3 adds the 3 days to the value in 'Created' column
• To test the weekday you use: WEEKDAY(Created)=4. In this case testing is on Thursday.
• If it is Thursday add 2 otherwise add 0 days would look like: If(WEEKDAY(Created)=4,2,0).
• The entire formula is therefore:

Created+3+IF(OR(WEEKDAY(Created)=4,WEEKDAY(Created)=5,WEEKDAY(Created)=6),2,0)+IF(WEEKDAY(Created)=7,1,0)
Of course, if you have to exceed a full week you will need to work harder to add more than 7 days as more than one weekend might be involved.
Add a Date Calculated Column:

Enter the Formula:

Show Last Month SharePoint List Items

This article has been lost and rewritten (but not tested)

Add these 4 columns: (the last one is needed only for current month calculations in case you need it.)

Date is defaulted to today.
Month =MONTH(Date)

Last Month – Start column  =DATE(YEAR(Date),(Month+1),1)

Last Month – End column  =DATE(YEAR(Date),Month+2,1)

This Month column =DATE(YEAR(Date),(Month)+1,1)

Create the Last Month  view with the filter:

Create This Month view with the filter:

Wrap the Title (name) of SharePoint List Columns

If you have a SharePoint list or Library with long column names the views might look awkward because the column name is spread out in one line while its content may be as a short as Yes/No.

To work around this issue, add a Content Editor Web Part to the list and type 3 lines of code into it. The result will look like this:

Here are the steps to achieve such results:
(the sequence is for 2007 release. 2010 is pretty similar)

• Navigate to the list.
• In Site Actions select Edit Page.
• Click Add a Web Part and navigate to select the Content Editor Web Part.
• Click the link inside the WP or click Edit to open the WP settings area on the right.
• (see an image at the bottom)
• Click the Source Editor... to open it.
• Enter the following code:  (within the style tags)
  
  td.ms-vb {white-space: normal !important}
• for 2010 release use: th.ms-vh2 {white-space: normal !important}
  
  
• 
  
  Okay the operation and Exit Edit Mode.
  Tip: Don't forget to hide the WP title by changing its Chrome Type in Appearance to none.
• The titles should now be wrapped.

WP Setting area is on the right: