So this is a follow up from my question yesterday as I believe I have found a work around or at least a potential one.
To cut a long story short I have a list called "Team Information" this contains 2 columns:
I am now creating a new list which will contain 2 look ups which will need to show options based on department for example:
Lookup 1 - Only show users within Finance
Lookup 2 - Only show users within HR
Yesterday I was looking for ways to achieve this but think I have come up with a solution that just needs a little tweaking. I have created a calculated column in the Team Information list with the formula:
I repeated this for HR and have used these columns as the lookups and now in the dropdowns the only information displayed is names of people within those departments however those not within the specified department are still showing as blanks in the dropdowns. Is there any amendments I can make to the formula to stop these blank values from appearing? I have tried a few but I'm terrible with formulas and can't seem to create one to achieve this.
Thanks in advance!