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


Top 5 Contributors of the Month
satyapriyanayak
Jean Paul
SP
Sambanthamoorthy
Swati Kadam
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.

  

Hi,

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,

S'


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:
ERROR
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?

Thanks,
Max


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.

Thanks,

 


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?

Thanks.



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.
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
<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

  

Hi,

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.
Categories: 
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