locked
Caluclated Column as a site Column(?) RRS feed

  • Question

  • Hello,

    I have 3 site columns that are made up of choice data.  I am trying to create a fourth column that is a calculated column that references these 3 site columns.  I am getting an error from sharepoint that is telling me the 3 columns used in the formula do not exist.

    The formula refers to a column that does not exist.

    Does anyone know why this is or if there is a way to get around this issue?

    Sincerely,

    Tim

    Monday, March 14, 2011 7:14 PM

Answers

  • By default, you cannot reference lookup fields in a formula.

    Check out this thread:

    http://social.technet.microsoft.com/Forums/en-US/sharepointgeneral/thread/467978fd-5db2-4af0-b4fc-d18e87316473

    • Marked as answer by Leoyi Sun Wednesday, March 23, 2011 7:58 AM
    Monday, March 14, 2011 7:53 PM
  • Hello,

    I have apparently found a solution:  to enclose the column names in brackets.  Do not remove the paranthesis though.  So if the formula is:

    =IF(LEN(Month)=0,CONCATENATE(Year,"-",Quarter),IF(LEN(Quarter)=0,CONCATENATE(Year,"-",Month)))

    You will want to modify the statement as such:

    =IF(LEN([Month])=0,CONCATENATE([Year],"-",[Quarter]),IF(LEN([Quarter])=0,CONCATENATE([Year],"-",[Month])))

    The only issue I noticed from the above is that if I go back into the column to edit teh formula, the brackets have been removed by SharePoint.  If I cancel out, the column still functions as designed.  It's just troublesome that the brackets are being removed.

    Sincerely,

    Tim


    • Edited by Mike Walsh FIN Saturday, May 7, 2011 5:38 AM line removed prior to lock
    • Marked as answer by Mike Walsh FIN Saturday, May 7, 2011 5:39 AM
    Monday, March 14, 2011 8:02 PM

All replies

  • By default, you cannot reference lookup fields in a formula.

    Check out this thread:

    http://social.technet.microsoft.com/Forums/en-US/sharepointgeneral/thread/467978fd-5db2-4af0-b4fc-d18e87316473

    • Marked as answer by Leoyi Sun Wednesday, March 23, 2011 7:58 AM
    Monday, March 14, 2011 7:53 PM
  • Hello,

    I have apparently found a solution:  to enclose the column names in brackets.  Do not remove the paranthesis though.  So if the formula is:

    =IF(LEN(Month)=0,CONCATENATE(Year,"-",Quarter),IF(LEN(Quarter)=0,CONCATENATE(Year,"-",Month)))

    You will want to modify the statement as such:

    =IF(LEN([Month])=0,CONCATENATE([Year],"-",[Quarter]),IF(LEN([Quarter])=0,CONCATENATE([Year],"-",[Month])))

    The only issue I noticed from the above is that if I go back into the column to edit teh formula, the brackets have been removed by SharePoint.  If I cancel out, the column still functions as designed.  It's just troublesome that the brackets are being removed.

    Sincerely,

    Tim


    • Edited by Mike Walsh FIN Saturday, May 7, 2011 5:38 AM line removed prior to lock
    • Marked as answer by Mike Walsh FIN Saturday, May 7, 2011 5:39 AM
    Monday, March 14, 2011 8:02 PM
  • Tim,

    I am sorry, I wrongly assumed you were trying to use lookup fields in your calculated column.

    I have always found that double-clicking on the available fields to the right of the forumula box is the best way to add fields to the formula.

    • Marked as answer by Leoyi Sun Wednesday, March 23, 2011 7:58 AM
    • Unmarked as answer by Mike Walsh FIN Saturday, May 7, 2011 5:39 AM
    Monday, March 14, 2011 8:16 PM
  • I am getting this same problem of the brackets disappearing in the forumula entry box.
    Friday, May 6, 2011 8:28 PM