4

I am new to SharePoint.

This is List A where the calculated column (Supplier) have to refer to list B enter image description here

This is list B enter image description here

This is the formula in the excel that I tried with: =IF((Sheet1!A:A = A2) * (Sheet1!C:C = "pass"), Sheet1!B:B, "no result")

I understand when referring another list, the syntax is different in SharePoint slightly.

How can I change the formula for the SharePoint?

Kindly assist.

Thank you.

Improve this question
2
  • There are several ways to do this. You should consider InfoPath and PowerApp as these two give you more flexibility than a simple formula. I have never done this with calculated Column but It can easily be done with Lookup Column.
    – Ehsan Aliverdi
    Commented Jun 24, 2019 at 4:42
  • Hi, could you elabore more on how it can be done by lookup column?
    – sangasangasanga
    Commented Jun 24, 2019 at 7:11

2 Answers 2

Reset to default
5

As far as I know, it's impossible to use other list's field in calculated column. As a workaround, I would suggest you create a workflow to achieve this.

Use the if condition in the workflow to refer to other list, then update field in current list.

Improve this answer
4

First and foremost you need to create a lookup column in list A to list B to link the items together. However you cannot reference a lookup column in a calculated column, so that approach will not work regardless of the syntax (Which is in fact the same as in Excel).

What you CAN do is to make a workflow that copies the text from your lookup column into a new field (simple line of text field) and then reference that field in your calculation in list A. This however has it's own drawbacks as the value in the new field will NOT change if you change the value in list B. In order for that to change you would need to make a workflow in list B that updates all items in list A with the new value - but that's only supported by using the REST api, which in turn requires you use SharePoint 2013 workflows.

So to cut it short, there's not easy way to handle this natively unfortunately.

Improve this answer
2
  • Thank you that cleared up a lot of my doubts. I understand lookup column cannot be referenced in the calculated column. But, why do I need a lookup column? You said to create a lookup column to link the items together. Do I have to do that to link the items together? Both lists have a common column, can't I use that as a linkage?
    – sangasangasanga
    Commented Jun 24, 2019 at 6:16
  • Well you could do that, but there's not really any point in doing that. You would have to rely on string matching rather than matching by id (lookup columns store the ID of the item) which introduces a higher risk of errors. And more importantly it does nothing in terms of solving your main issue :) The easies way to do what you want is to skip list B and simply create a choice field in list a of the suppliers adding "(pass)" or (fail) after their name, Then you can extract the value from the supplier name directly :)
    – Morten K
    Commented Jun 24, 2019 at 6:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.