I have List A which has all information.
List B has Col 1 that looks up one of the List A's columns. Col 2 is a Calculated column that want to use Col 1 but SharePoint is not able to see Col 1 because it's a Lookup column?
In the Calculated column; 1) Are we able to use a column from another list? 2) Is there anyway we could use a Lookup column?
Thank you guys!
The information that's stored in a lookup column is actually just a pointer to the list item in the first list, not the data itself. As such, SharePoint doesn't know what to do with this data. Essentially, if you want to perform calculations on it you will need to physically copy those columns to the second list. I realize this violates all sorts of database normalization things but SharePoint is not really database software.
If you just need to update a record when a new item is created and it doesn't really ever change, you could create a SP Designer workflow on the first list to expert the data to the second. If you need to do the equivalent of full CRUD operations, you may instead want to look into one of these options:
Set up a chain of event receivers with that first list on ItemAdding/Added, ItemUpdating (the -ing so you can query whether or not the field you're copying over was edited before you perform the operation), and ItemDeleting. The biggest issue with this is that if for some reason you or someone else then chains the second list back to the first for whatever reason you're risking setting up an infinite loop. SP tends to catch these when they're, for instance, one event receiver operating against another, but 3rd party software doesn't always do this and SP might not catch 2 different solution types interacting like that until you wake up one morning to find that your server has crashed.
Use an externalized database to store this information. If you set up BDC well, views you create off of it should look and behave exactly like any other SharePoint list, and you can set up the CRUD operations yourself on the back end to put the data where it needs to be put.
Use Access Services. If you don't really have access to a separate external server and you only really need this data in one site, you could just set up what amounts to an Access DB that folks go to to enter this data. It doesn't really look like SharePoint anymore and IME programming Access is kind of a pain but depending on your resources this may be your best option.
If you're using SP2013 and the App Model, the 2nd option is probably the best way to go.
Lookup columns cannot be used as input in calculated columns in the browser interface. If you have SharePoint Enterprise, you can use InfoPath to edit the list form and populate Col 2. In InfoPath you have a much bigger toolset at your convenience.
Another alternative would be to use a Sharepoint Designer workflow on item creation and edit that calculates the desired information and updates a column (single line of text) with the information.
