.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Melody Anderson
Post New Web Links

Calculated Columns - How to Use " as a text in a formula

Posted By:      Posted Date: October 15, 2010    Points: 0   Category :SharePoint

Hi there,

In Excel you can use """ to add " to a formula, but this does not seem to work in SharePoint Calculated Columns.

What I'm want to achieve is an output of "Column1"; and I've tried using the formulas ="[Column1]", ="""[Column1]""", ="&[Column1]&", ="""&[Column1]&"""

I'd be very grateful if anyone can tell be a way of incorporating quotes as text into a SharePoint calculated formula.

Thank you, in anticipation.

Ian Hearnes

View Complete Post

More Related Resource Links

Calculated Columns in a Form Library receive error: Value does not fall within the expected range.


I have a forms library that posts the date the form was created. We would like to create a calculated column that allows us to look at these forms based on the month and year they were created. I had created a calculated column that extracts the month and replaces the number with a text string by using an if statement; "IF(MONTH([Proposal Date])=1,"January","") It goes on to evaluate each month and replace the number with the text string. This formula worked for quite some time, though now when I try to add any calculated columns or edit any existing calculated columns on this form library I receive the following error: Value does not fall within the expected range. Now my existing calculated columns (like the one above) do not work and I can not create new calcualted columns. Any insight would be awesome as this has been plaguing me for a couple of months now.

How to sum Calculated Columns

Hi, I want to sum the calculated column (currency format). I found some ways to do that in SharePoint 2007 but how to achieve this in SharePoint 2010? I  try to use SPD 2010 but no result. Thanks in advance!   

Errors with calculated columns, the column posts, but doesn't calculate.



I have a custom list that isn't processing calculated columns anymore.

When I post a calculated column I get one of two error messages...either "SQL Server may not be started" or "The value does not fall within the expected range".  I've noticed the sql message before but the changes are made and calculations in the past have worked ok.  Now that the "value" error is cropping up it's quit calculating the columns.

i.e. I open a calculated column, simply have it reference a numeric field (=[MyNumberField]).  When I hit ok I get one of the two error messages.  When I look at the value of the calculated column it's blank.

Any references to articles, etc. would be helpful.  There are 406 columns in this list...

Best Regards,


Calendar View using Calculated Columns


I've got a large list of documents that undergo regular maintenance.  To help facilitate this, we had an Excel spreadsheet (recently converted into a Custom List in SharePoint) containing columns with "Date Last Revised"(Date), "Effective Period"(number), "Delay"(number), and "Next Revision Start Date"(calculated; Date Last Revised + Effective Period + Delay).

This works fine, until you get into SharePoint.  My columns are:
   Date Last Revised - Date and Time (Date Only) format
   Effective Period - Number format, Default Value = 365 (1 year)
   Delay - Number format, Default Value = 0
   Next Revision Start Date - Calculated (based on other columns) format; sum of other three columns

The Calculated column works fine, and will calculate a Date based on the values of the other columns.  HOWEVER, my ultimate goal would be to have a Calendar View of this information, where the Start/End dates are the "Next Revision Start Date" column.

When I try to use any other Date column, it works.  When I try to use the calculated "date" column, it gives me a generic error message:
An unexpected error has occurred.

I don't really want to lose the functionality of each item being able to have separate effective period and delay values, but any informatio

How can I make line breaks to calculated columns?

Hi. I have a problem regarding SharePoint lists. I have several single line text columns on my list and now I want create one combined column by using calculated column. That I can do just by using following formula: [column1] & ", " & [column2]...but I would like to have the final result so that each column value is located in own line. Question is how to do these line breaks to these formulas? I have tried [column1] & "<br>" & [column2]... but it didn't worked.

Calculated Columns

I am trying to utilize calculated columns to give me specific date/time data.  When I create a calendar event in SharePoint a "Start Time" value is created which looks something like 2009-10-07... Now what I would like to do is create a custom column for the year, month, and day individually.  I however can not figure out how to write the Custom Column Formula to accomplish this.

End result would provide me with a column for Start Time, Day, Month, and Year.  Any help is greatly appreciated.  Thank you!

Passing Calculated Columns from Sharepoint into Word Document Library Template


Hi all...

Another noob question.  I have been able to figure out how to pass single line text, date, etc. data from a Sharepoint List into a Word Template, but I am still trying to figure out how to pass a calculated column.  Is there a workaround?


calculated columns in List Definition.


Hi all,

I have a custom list definition and I need to include a calculated column Days open in it which says that the number of hours /Days elapsed after the item has been created. I am trying to use predefined column Created to get the date created and I am in a confused state of how to include the today date functionality and get the difference in terms of Days /Hours.Can some body guide me on how to implement this in my list definition .I need to achieve this by using pure XML but not by any coding.



Calculated column based on other columns in Lookup list selection


.. if that makes any sense.

I have a list with with lookup column to another list. that list has other columns.

I want to add a calculated column on the parent list, that set it's value to column from another list, but based on my selection in the lookup column of the parent.

Possible? presuming the calculated column  gets set when I submit and not interactively? what if edit the data and change my selection?


SharePoint Calculated Columns with Conditional Formatting

I am working on a custom list in SharePoint 2007. I have a calculated column that is formatted as a percentage. When opened in SP Designer and converted to a XSLT data view to apply conditional formatting, the column is rendered as text and not numeric which is a known issue with SharePoint. How can I apply formatting to this column and render it as a numeric value? My conditional formatting relies on If x is <= y then apply yellow and so forth so it has to be read as a numeric value. I have attached the conditional formatting code as well as the format of the data property. Any help would be appreciated.
<TD Class="{$IDAS4QMC}">
																<xsl:attribute name="style">
		<xsl:if test="@_x0025__x0020_Variance_x0020_C &gt; '-.11' and @_x0025__x0020_Variance_x0020_C &lt;= '0'">background-color: #00FF00;</xsl:if>
		<xsl:if test="@_x0025__x0020_Variance_x0020_C &gt;= '.1'">background-color: #FF0000;</xsl:if>
		<xsl:if test="@_x0025__x0020_Variance_x0020_C &lt; '-.11' or @_x0025__x0020_Variance_x0020_C &gt; '0'">background-color: #FFFF00;</xsl:if>
		<xsl:if test="@_x0025__x0020_Variance_x0020_C &gt;= '-.11' an

How to validate the value of date columns and use its value in the Calculated column



I am using SharePoint 2010. I have 3 columns in the list:
1) Start date
2) End date
3) Description

I have 3 following scenarios:

·         Some records have value of only Start date and "Description"

·         Some records have value of only End Date and "Description"


MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.

Adding Multiple Columns and Rows in GridView without using a Database

This article describes on how to add multiple columns and rows in GridView without using a database. Basically the GridView will be populated with data based on the values entered in each TextBoxes on Button Click and retain the GridView data on post back.

add columns with textboxes in gridview using C#

'm working on a shopping cart in a website and I have my items(which have been added to the cart) in an arraylist. And using these values I've read the rest of the values from the db and and have populated on the gridview successfully.

Custom Editing Behavior for DataGridView TextBox Columns

For various reasons I'm using a DataGridView instead of a ListView, and while configuring the DataGridView to look like a ListView wasn't much of a problem, there's one thing that got on my nerves, which is the behavior of textbox cells in edit mode: It is much too easy to leave the edit mode accidentally, simply by pressing the cursor keys at the wrong time. For example when the text caret is positioned behind the last character of the textbox cell content, and you press the right arrow key

Sorting GridView Columns Manually

We all know that GridView columns can be sorted automatically when SQLDataSource is being used. But what if you are not using SqlDataSource to populate the GridView. Sorting the GridView manually is pretty straight forward task take a look at the code below

dynamically adding sql server columns based on number of files in fileuploader


I want to create a new table to sql database at runtime.  The column names would be "Name", "Date", "Event".  I would also like to have one more column name "Image" but the problem is that since the amount of images will vary, I want the application to count the amount of files in the Multiple fileuploader and then add a image column to the table for the respected amount of files. 

For example if i am uploading 3 files in the multiple fileuploader, the names should be "Name", "Date", "Event", "Image1", "image2", Image3"

I also would like that table name to be name dynamically from the textbox Name when the user enters the information.


I have the following code to make a new table, but I dont know how to name the fields at runtime like previously asked please help!!!


Dim objConn As New SqlConnection("Server=<servername>;uid=<userid>;pwd=<password>;database=master")


ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend