Lookup value in column to display name

I've got 2 columns - Name ID and Name.

When a user adds a new entry to the sharepoint datasheet in the form, th开发者_StackOverflow社区ey have a dropdown list of all names (around 20 in total).

What can I do, so that when they select the name, the Name ID field auto-populates with its corresponding details?

Is there somewhere I need to hold this data and how do I go about creating these calculated columns?

Thanks in advance

You should just be able to do something like this

=TEXT([NameIDcolumn])
# Or
=CONCATENATE("You selected:", [NameIDColumn])

However, you cant use the value from the ID column or any think like that in a calculated col since the value does not exist yet. The SharePoint dev team was most likely smoking crack when creating that datatype since a lot of good stuff that should have been there isn't.

You have the complete reference of stuff you can make with calculated columns here http://office.microsoft.com/en-us/windows-sharepoint-services-help/CH010065006.aspx