Editing formulas directly in SharePoint Lists using the datasheet view

Calculated columns in Windows SharePoint Services 3.0 Lists are quite powerful, allowing you to use many Excel functions and operators to massage data in the current list using the data from other columns.  After you create your formula in a calculated column, if you ever need to make changes you need to go to the Settings page for that column and edit it.  Right?  Right??  No!

Here’s a sample list containing the URLs of sites that need to be exported using the stsadm.exe tool.

<lost image>

I’ve  created a calculated Export Command column that will automatically create the parameters needed for the stsadm command.  If you want to play along with the home game, create a custom List using the following fields:

URL: Text (I just renamed the Title field)

Relative URL: Text

Export Command: Calculated Column

Formula:

=CONCATENATE(“stsadm -o export -url “,URL,” -filename “”C:Migration”,[Relative URL],”.bak”””)

I’d make the Relative URL column calculated as well, except the SEARCH and FIND functions do not allow you to start from the end of a string, and I can’t think of any way to find the last slash and get the text immediately after.  It’s easy if you know that http://servername is a fixed value – just use a MID function with the starting position of the last slash!

Anyway, put some sample data in your list, switch to Datasheet view and double-click any cell (even an empty one) in the Export Command column.  Look at this:

<lost image>

It replaces the data with the formula, allowing you to edit it in place.  When you are done, press ENTER and the column’s formula is updated with your changes:

<lost image>

Pretty handy!

Eric Legault

Microsoft Outlook MVP (15 yrs). Author: Microsoft Office 2013 Pro Step By Step. Consultant/speaker/Outlook add-in builder/SharePoint pioneer. Guitarist: Maiden Canada (Iron Maiden tribute band)

You may also like...

%d bloggers like this: