Ok, so, you create a reference list that will hold all… say… document types for a company… this list will hold every document type in one list… you add a document category too, thinking.. ‘Cool, I’ll just use this lookup list in all of my document libraries and filter by the category…’
Yeah… about that…
Not going to happen…
There is no filtering ability where lookup column are concerned.
We could create individual lists containing documents types for each category, but, that defeats the object of what we’re trying to do. We want to manage all of the document types in one list…
So, a work around…
In your reference list, you’re going to have to create calculated columns for each ‘filter’ you want to use… So, one calculated column for each category.
Name it something meaningful 😉 so, if this filter will be ‘Employee Documents’ call it something like EmployeeDocs…
Now for what makes it work.
In the formula, we’re going to use the IF function (here is a link to the Microsoft Documentation).
The syntax is:
=IF([ColumnToTest]TEST, WhatHappensTrue, WhatHappensFalse)
This would place the contents of the Title column in the New column if the category is Employee, otherwise will leave empty.
This gives us a list like :
Now, when using this new column as a lookup, it will only include entries with something in them, it will not include the empty entries.
Add a Lookup column the usual way, selecting the reference list as the target, then select your ‘filter’ column as the data returned
Of course, the way that SharePoint allows access to lists only from the same site, means that if we wanted to create this list and access it across all sites in the collection, we would now need to add the filter columns to the Site Columns.
A bit clunky, and a bit of a pain to initially set up, but, once setup it pretty much takes care of itself.