1

I have a DB on my SharePoint site using several Lists. For one of the lists I need to count Total, Pending and Completed records from the other tables.

There's one list for each department and on it each user has a number of procedures they must read if the procedure has been completed then it's set to Completed otherwise will show Pending. So I need three calculated values in my Users list, Total will count the number of items for the user and will select the table according to the department. Pending will count only the items set to Pending and Same for completed.

My lists are like this:

HR (Procedure Name, Status, Name, Email)
IT (Procedure Name, Status, Name, Email)
Marketing (Procedure Name, Status, Name, Email)
.
.
.
Users table has the following columns
Name, Email, Department, Total, Pending, Completed

So if the user belongs to the HR department then the Total, Pending and Completed columns will be fetch from that list. Is there a way to achieve this using the Calculated Value column?

1
  • No, in calculated column, you can't retrieve values from columns in other lists. Any specific reason that you're storing data into separate departments. You could add a column called [Department] and store all items in one list and then use [Group By] to count Total, Pending, and completed. – Matiur Rahman Dec 17 '20 at 21:23
2

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 MS Flow to achieve this.

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

1

No, It's not supported, please check The list of supported and unsupported columns in SharePoint calculated column


Workarounds

  • Create a lookup column from another ist in your current list then create a workflow to use this value and update your list item.

Check the detailed steps at How to use Lookup field in SharePoint Calculated Column Formula.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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