none
Error message: The formula contains reference(s) to field(s).

    Question

  • We are using WSS, 2003

     

    I created a site and have columns on the site

     

    I have a date/time column called DateWorked.  It is a required field.

     

    I am trying to get date field calculations to work based on another
    existing date field within the site.  I am starting out simple, I want to have another
    date/time field be a calculated field based on the DateWorked field.
    I create the new date/time field, set it to date and time for type and format,
    set it to be a calculated value and enter the formula:
    =[DateWorked]

    I receive this error message:


    The formula contains reference(s) to field(s).


    Also, if I create a text field and indicate that it is a caluclated field based on another
    text field using a formula as simple as =TITLE
    I still get the above error message.

     

    Does anyone know of a solution to be able to refrence fields on your site within the calculated value field?

    Friday, July 6, 2007 2:50 PM

Answers

  • Nope.

    As previously stated by Curtis, you can't use references to fields in a Calculated Value (computed at the creation of a new item) ; but you can use them in a Calculated Column.

    [Me] and [Today] are "system" fields instead of being related to the current list, thus making them available for use in a Calculated Value. On the other hand, you might have trouble using those 2 in a Calculated Column, but there exists a workaround.
    • Marked as answer by GuYuming Thursday, May 13, 2010 5:21 AM
    Wednesday, August 1, 2007 9:21 PM
  • You can't do a calculated value on a field for a default value, since the field you are referencing doesn't have a value yet.

     

    If you want to create a readonly calculated field type, you can certainly use columns.

    • Marked as answer by GuYuming Thursday, May 13, 2010 5:19 AM
    Friday, July 6, 2007 3:34 PM

All replies

  • You can't do a calculated value on a field for a default value, since the field you are referencing doesn't have a value yet.

     

    If you want to create a readonly calculated field type, you can certainly use columns.

    • Marked as answer by GuYuming Thursday, May 13, 2010 5:19 AM
    Friday, July 6, 2007 3:34 PM
  • I am having the same issue. The forumla worked in the previous version of sharepoint on SBS Server, however, does not work on WSS3. Here is the working forumla in the previous version:

     

    =IF(AND(LEFT([Company Name],1)>="A",LEFT([Company Name],1)<="F"),"A-F",IF(AND(LEFT([Company Name],1)>="G",LEFT([Company Name],1)<="N"),"G-N","O-Z"))

     

    Any suggestions would be appreciated.

     

    I do not understand why this would work in a previous version of the product and not the newer version. Help is not helpful in this case.

     

    Charles

    Friday, July 20, 2007 3:32 PM
  • Is this a default value calculation, or a calculated field?
    Friday, July 20, 2007 4:17 PM
  • Curtis or anybody that can help,

          I want to say i keep getting the error "The formula contains reference(s) to field(s). " too

    I am trying to join 2 strings in a Contact List Firstname and lastname for example using any of these examples:-

    Nancy Fuller =Column1&Column2 Combines the two strings (NancyFuller)
    Nancy Fuller =Column1&" "&Column2 Combines the two strings, separated by a space (Nancy Fuller)
    Nancy Fuller =Column2&","&Column1 Combines the two strings, separated by a comma (Fuller, Nancy)
    Nancy Fuller =CONCATENATE(Column2, ",", Column1) Combines the two strings, separated by a comma (Fuller,Nancy)

    But no luck

    But when i do =[Me] or ="You are" & [Me] it does work showing me "You are MYUSERNAME".

    Am  i missing something or am i supposed to enable or create something.

     

    Thanks in advance

     

    Tuesday, July 31, 2007 1:51 PM
  • Have you guys been able to solve this.

    its driving me nuts

    Wednesday, August 1, 2007 12:24 AM
  • Can youn please elaborate on this..

    Are you saying if i have a Column with a Default value marked as Text let say COLUMN called FirstName,LastName etc..

    Can't i create another column and mark it as a Calculated vale and then join the 2 columns by doing so

    =FirstName&LastName

    Thats exactly what i'm doing but keep getting error .

    If i use [Me] instead it does work

     

    No idea whats going on
    Wednesday, August 1, 2007 1:42 AM
  • Nope.

    As previously stated by Curtis, you can't use references to fields in a Calculated Value (computed at the creation of a new item) ; but you can use them in a Calculated Column.

    [Me] and [Today] are "system" fields instead of being related to the current list, thus making them available for use in a Calculated Value. On the other hand, you might have trouble using those 2 in a Calculated Column, but there exists a workaround.
    • Marked as answer by GuYuming Thursday, May 13, 2010 5:21 AM
    Wednesday, August 1, 2007 9:21 PM
  • Ludovic,

           Thanks for the detailed explanation.Seems i didn't go through the tutorials well Sad

    So whats the workaround in using the system fields like [Me] and [Today] in a calculated colums?

    Thanks in adv

    Thursday, August 2, 2007 12:36 AM
  • Thursday, August 2, 2007 3:25 AM
  • Yes, That is a default value calulation.

     

    For adding a calucated column based on other column, follow the below steps

    1. On the Settings menu of the list, click Create Column.
    2. In the Name and Type section, type the name that you want in the Column name box.
    3. Under The type of information in this column is, click Calculated (calculation based on other columns).
    4. In the Additional Column Settings section, enter the formula that you want to use in the Formula box. You can select items from the Insert Column box and then add functions, constants, and operators to the formula.
    5. Select the data type that you want to be returned by the formula, and then click OK.
    • Proposed as answer by Jowiebear Thursday, June 3, 2010 7:30 PM
    Monday, March 3, 2008 10:17 PM
  • I had the same error while trying to modify a field ("Full Name") in a list ("Contacts"), it didn't work.

     

    I simply created a new field ("Contact Name") and selected type "Calculated" (this is very important) and entered this formula : =[First Name]& " " & [Last Name] in the box, and it works fine

     

    Z.

     

    Wednesday, March 26, 2008 9:11 AM
  • Hi everyone,

     

    hope you could help me with my MOSS problem here.

    I have a custom list entitled "Status Report"

    part of its columns/fields are Project Name & Project ID (both required fields)

    The "Title" field is a calculated column to display the following format: Status Report - 1/1/2008 2:22:00 PM

    I have already implemented that, the question is, in the AllItems view, when you click the hyperlink Status Report - 1/1/2008 2:22:00 PM I should go to a page wherein, this Title will be hidden and will be replaced with a field of the following format: Project Name - Project ID.

    Also the header (the one in color light orange), would also display Project Name - Project ID

     

    How would I implement this two?

    I tried creating a new calculated column with formula =[Project Name]&" - "&[Project ID]

    and I try setting it to the Status Report Title field.

     

    But I always get the error: The formula contains reference(s) to other field(s). I know this happens especially because when those fields do not have entries yet. But even if i try using IF statement for this, it still doesn't work.

     

    Thursday, May 29, 2008 3:55 AM
  • @Ludovic Chungue
    The solution in this blog's article could be nice but the date is not dynamic. Once you've created your column [Today] (see article) it won't change the day after.
    Anthony
    Monday, April 20, 2009 1:44 PM
  • OK -- so is there a workaround to the inability of a Sharepointcalculated value default to draw on other columns? I'm trying to supply a default value for a column, so that users are offered a unique value that does not duplicate the values in other rows. The field's format is 'UT000001', 'UT000002' ...., but that number must be overwritable with other text values if wanted (don't ask for the full explanation, it's too involved)

    My first shot was to create a numerical column by singling out the 'UT...' values only, lopping off the UT, placing these in a calculated column then applying a strategy of MAX value plus one (I hoped to pull this in as a default value calculated value  for a different column)-- but I coudn't find a way in SharePoint to create a MAX value for a whole column, particularly a calculated one, so that strategy seemed to fail quite early on.

    My second strategy was to build the UT number by appending it to the column ID (dispensing with sequential UT #s, but still assuring they were unique. This creates a value for the intended default -- but I can't get make it appear as a field default, only as a calculated field that cannot be changed.

    Does anyone have a method I might use to achieve the goal: to supply a unique default value for a field that WILL permit overwrites?

    Thanks.

    PLEASE ALSO SEE THE NEXT POSTING
    • Edited by old brit Tuesday, June 16, 2009 5:05 PM
    Tuesday, June 16, 2009 3:35 PM
  • I was wrong, this also failed to return the correct value when creating a new record. It calculated for existing records fine -- but for a new record I presume the ID had not been assigned by the time the calced field was created, so I get 'UT000000' as the value for each new record.

    The code I used for the 'default' column was

    = CONCATENATE("UT",RIGHT(CONCATENATE("A",[ID] + 100000),LEN(CONCATENATE("A",[ID] + 100000))-2))

    which builds a literal string from the ID and then manipulates it into the form I wanted. Except that the ID must have been zero when it calculated  for the new record!!

    So additional advice would be useful -- neither of my strategies met with ANY success.

    Thanks.
    • Proposed as answer by Barbara-Ann Tuesday, June 30, 2009 4:50 PM
    Tuesday, June 16, 2009 5:05 PM
  • The question I'm answering is: How do I create a ticket# type of field for a SharePoint list - perhaps a list of tasks, issues, etc.

    Formulas can only include current row data,which makes it difficult to then use logic that would increment from the most recent row. 

    I initially tried to do this by adding a column that calculated        =TEXT([ID],"00000")

    Calculated columns don't work  because the ID field is generated when the item/row is committed.  The calculated field calculated before ID has a value, and the formula is not dynamic and won't recalculate later.  This looks like a bug, although officially a bug is something that doesn't work as intended. 

    What did work was the Data View .  Here's how: In SharePoint designer, insert a data view, and make a column out of the formula format-number(@id,'00000') .  You can even get fancy and concat with concat('AR',format-number(@id,'00000')) .  This will get you 00023 or AR00023.  

    The reason this works is that you have no need to store the calculated ID if it can always be derived from the row ID. 



    • Proposed as answer by Barbara-Ann Tuesday, June 30, 2009 4:50 PM
    • Edited by Barbara-Ann Tuesday, June 30, 2009 5:04 PM
    Monday, June 29, 2009 11:57 PM
  • Exactly what I needed! I have a Time Reported and Time Resolved column, and wanted a Downtime Duration column to be calculated based on those two columns, Bajju's instructions were exactly what I needed to get it working!

     

    Thanks!

    Thursday, June 3, 2010 7:32 PM