SharePoint: Not all column types can be used in Calculated or Lookup columns

A recent question in the MSDN forums got me to looking again at the limitations on the use of certain column types in calculations or lookups. The list of column types that can be used in lookups is quite limited, but with a trick using a calculated column we can add four more to the list.

For my test I added all of the following columns to a custom list. These include the basic columns plus a few variations of options including each type of calculated column.
image

Types that work for a Calculated column

I then added a Calculated column. This is the list of fields offered for use in a calculation:
    image
Not included in this list were these columns:
    image

Types that work for Lookups

I then added a lookup column to another list to see which column types could be used for lookups. Here’s what shows up:
    image
The only column types available for lookups are:
    image
And when a lookup has been selected, here is the list of columns that can also be displayed from the lookup list:
    image
Or, just these types:
    image
So, these are excluded from lookups:
    image

A workaround for some missing lookup column types!

If you look back at the types that did show up in the list for lookup columns you will find:
    image
And if you go back to the list of types that can be used in calculated columns you will see that we can create calculated columns for some of the missing lookup types, as long as the calculation returns “Single line of text”!
With a calculation we now get to:
  Yes / No
  Choice (Drop-down or Radio Buttons, but not checkbox)
  External Data (and external data additional columns)
  Currency (but without the currency ($) symbol)
The calculation for Choice and External data is pretty straight forward:
   =[fieldname]
The currency type will just return the value, with commas and decimal points. You may want to add the currency symbol.
   =[fieldname]
or
   =”$” & [fieldname]
The Yes / No is a bit of a problem as the simple calculation just returns a 0 or 1. To get the words Yes and No you will need to do just a little more work:
  = if( [yesnofieldname], “Yes”, “No” )
Remember for all of these you will still need to set the "data type returned" to "Single line of text".

A few additional resources for what column types work where…


SharePoint Columns – Features and limitations
http://www.sharepointusecases.com/index.php/2011/07/sharepoint-columns-features-and-limitations/

Create list relationships by using unique and lookup columns
http://office.microsoft.com/en-us/sharepoint-server-help/create-list-relationships-by-using-unique-and-lookup-columns-HA101729901.aspx

Column types and options
http://office.microsoft.com/en-us/sharepoint-online-enterprise-help/column-types-and-options-HA010302193.aspx?CTT=1

SharePoint 2007 Supported Lookup Column Types
http://sharepoint.nauplius.net/2010/09/sharepoint-2007-supported-lookup-column.html