SharePoint calculated column text with data from another list?

Question

I have two lists in SharePoint, each with two columns:

List A

Column 1-----------Column 2

Bob-------------------Apple

Joe-------------------Orange

Jane------------------Banana

List B

Column 3-----------Column 4

Column 3 in List B is a lookup from Column 1 on List A, so there's a drop-down list of names in Column 3.

I would now like to set up Column 4 to auto-populate with the associated data in Column 2 from List A (i.e. if I select "Bob" in Column 3, Column 4 will populate "Apple". If I select "Joe" in Column 3, Column 4 will populate "Orange", and so on). I'm thinking this should be a calculated value but not sure.

Essentially, is it possible to set the value of a calculated column to data from another list? Or how else can I do this? Would it be a workflow, and if so, how? I'm working in SharePoint 2013, either just out of the box or I can do workflows. Thanks!

You can edit Column 3 field to add dependent lookup fields. It should fill your requirements. — Salah

Lee_MSFT Lee_MSFT · Accepted Answer · 2019-07-17T01:04:48

Similar requirement as this thread, so you could use JavaScript to populate Column 4 when Column 3 change in list new/edit form.

$(document).ready(function () {
            var ID;
            $('select[title= "Name"]').change(function () {
                ID = $('select[title= "Name"] option:selected').val();
                $().SPServices({
                    operation: "GetListItems",
                    async: false,
                    listName: "Test",
                    CAMLViewFields: "<ViewFields><FieldRef Name='Country'/>, <FieldRef Name='Location'/>,<FieldRef Name='Mobile'/>,<FieldRef Name='Manager'/> </ViewFields>",
                    CAMLQuery: "<Query><Where><Eq><FieldRef Name='ID'/><Value Type='Counter'>" + ID + "</Value></Eq></Where></Query>",
                    completefunc: function (xData, Status) {
                        $(xData.responseXML).SPFilterNode("z:row").each(function () {
                            var Country = $(this).attr("ows_Country");
                            $('input[title="Country"]').val(Country);
                            $("input[Title='Country']").attr("disabled", "disabled");                          
                            var Location = $(this).attr("ows_Location");
                            $('input[title="Location"]').val(Location);
                            $("input[Title='Location']").attr("disabled", "disabled");                            
                            var Mobile = $(this).attr("ows_Mobile");
                            $('input[title="Mobile"]').val(Mobile);
                            $("input[Title='Mobile']").attr("disabled", "disabled");                            
                            var Manager = $(this).attr("ows_Manager");
                            Manager = Manager.replace(/[^a-z ;]/gi, '');
                            var re = /;;/gi;
                            var re1 = / /gi;                            
                            Manager1 = Manager.replace(re, ';');
                            Manager1 = Manager1.replace(re1, ', ');
                            Manager1 = Manager1.substring(1);                            
                            $('input[title="Manager"]').val(Manager1);
                            $("input[Title='Manager']").attr("disabled", "disabled");                            
                        });
                    }
                });
            });

        });

SharePoint calculated column text with data from another list?

1 Answers