We have used the formula below to concatenate information such as document name and version, or to create unique IDs in SharePoint.
1. Create a Calculated value column
2. Use the formula below,
Note: Replace the generic ColumnName1 and ColumnName2 in the example above with the name of your respective columns.
Use the solution below if you are asked to provide a column in a SharePoint custom list that displays the day of the week based on another column that contains an actual date.
In the example to the left, the first column (Date) displays a date in the M/D/YYYY format, while the second column (Day) displays the day of the week for the date to the left.
The column to the left consists of a Calculated column.
The formula needed to translate the information in the Date column is:
Just replace Date with the (internal) name for the column applicable to your own list.
Use dddd to display the full name of the week (for example, 'Sunday').
Use ddd to display the abbreviated name of the week (for example, 'Sun').
For some reason that Microsoft probably only knows, you cannot create lookup columns to Choice columns within other custom lists. Lookups seem to only work on Single Line of Text type of columns, which doesn't make much sense to me. However, there is a work-around using Calculated values.
I discovered this once when I was trying to add a lookup column pointing to a very extensive list of countries, and a few other things, such as regions, divisions, etc.
The purpose of using Choice columns is for most part to force the user to make selections from a predefined list, so as to avoid duplicate values or mispellings.
Why would Microsoft not allow pointing lookup columns to choice columns is beyond me. But well, let's dive in the work-around I found. By the way, this works for both MOSS 2007 and SharePoint 2010.
So the first thing we need to do is create a custom list where to host the Choice column.
As you can see in the example image to the left, I've created the Country column as a Choice column.
The next step is to create the column that will actually be used for the lookup. I'm going to call this column LookupCountry.
I'm going to designate is a Calculated value column. And then I'm going to insert the Country column.
The next thing you need to do is determine the data type that will be returned from the column. For my example, I'm good using Single line of text; therefore, I won't change the default value suggested by SharePoint.
So now we need to go to the second list, from where we want to reference the list of countries.
Create a new column on this second list and select Lookup as the type of information.
Make any additional selections you may need (such as whether to require that this column must contain information).
Select your first custom list, the one where you created the calculated value column at the Get information from drop-down list.
And then select the Calculated value column from the list (where it says In this column).
Save everything and voilá! Once some values are input in the first list, the lookup drop-down on the second list should display some values.