SharePoint - Manager/Role Lookup

Copper Contributor

I'm trying to replicate the attached table in SharePoint Online, ideally using a Look-up list.
Ideally with Out the box functionality. I hope you can help.

 

Scenario:

 

List of Responsibilities (30)
3 Companies

 

Each responsibility is held be a Senior Manager, with roles such as:

 

  • Chief Executive Officer
  • Managing Director
  • Technical Director
  • Chief Risk Officer

  • The same individual may hold the same role at different companies (for instance the CEO).
  • Some of the companies have a different person holding the position (for instance, different Managing Directors)
  • Some companies do not have the role at all.

 

In my experiments I have two lists - one 'Responsibilities' with a 'Responsibilities' column (A, B, C etc) and a corresponding 'Description', with look-up columns to a second 'Senior Managers' list.

I've tried a number of things in the look-up list, for instance:

 

Company // Role // HeldBy
------------------------------
Company1 // CEO // James Stephens
Company1 // MD // David Richards
Company1 // MD2 // Martin Price
Company1 // Tech Director // Steve Gates
Company2 // CEO // David Richards
Company2 // MD // David Richards
Company2 // Chief Risk Officer // Simon Templar
Company3 // Chief Executive Officer (Group) // James Stephens

 

OR

 

Company1:CEO // Company2:CEO // Company3:CEO // Company1:MD // Company2:MD // Company3:MD ... etc
----------------------------------------------------------------------------------------------------------
James Stephens // David Richards // James Stephens // David Richards // David Richards // Martin Price

 

OR

 

Company // CEO // Managing Director // Technical Director
----------------------------------------------------------------
Company1 // James Stephens // David Richards // Steve Gates
Company2 // David Richards // Martin Price // [EMPTY]
Company3 // James Stephens // David Richards // Bill Wozniak


However when you select the look-up dropdown in the 'Responsibilities', you tend to get duplicated values such as "Company 1, Company 1, Company1" which doesn't help usability.

 

Ideally I don't want the Responsibilities duplicated on the same list for 3 companies.

 

Hope I've explained this well enough!


What's the best approach to replicate the table in SharePoint?

I want a good user interface, so a role/manager can be selected for each responsibility, without the need to fill in too many fields (hence using look-ups).


Thanks so much.

1 Reply

Hello.  No replies.  Can anyone offer a solution to this?  Thank you.