Sharepoint: Calculated column based on another list

If you have multiple column field type and you use that as lookup field. You can get two column values in one field. It will show up as two different columns on list but you will need render templates for input forms. Check this out for basic idea

http://msdn.microsoft.com/en-us/magazine/dd727508.aspx#id0450138

• Create a field type for course list
• Create render template for MCV and extend SPFieldMultiColumnValue
• Make that field as Lookup Column

You have to check if you can do second step using SPD only. Also another way to do it is using Multiple column lookup field

HTH

Lookup fields will contain a combination of the row's id and the value of the column to display, separated by :#, in your case that could be 1:#HumanResources or 12:#Engineering.

So to reference a lookup simply setting the id won't be enough, instead the above mentioned string needs to be set. Luckily SharePoint provides the class SPFieldLookupValue that does exactly this:

var department = web.Lists["Department"].GetItemById(1);
var employee = web.Lists["Employee"].Items.Add();
employee["Name"] = account.Name;
employee["Department"] = new SPFieldLookupValue(department.ID, department.Title);
employee.Update();