Mapping job roles to access permissions

[eeb]

Hello,

I have two tables - one which maps employees to specific job roles, and another which maps the job roles to folder access permissions.

I am trying to make a third table, which will list all folders each employee should have access to, based on their job roles.

Example:

Sheet 1: Roles

First	Last	Product Development	Design	R & D	Deployment	IT
Employee	1	X	X	X	X	X
Employee	2	X
Employee	3			X
Employee	4	X		X
Employee	5	X			X	X

Sheet 2: Permissions

Permissions	Product Development	Design	R & D	Deployment	IT	Quality Assurance
Section 1
Folder 1
Folder 2		A				A
Folder 3	A	A	A		A	A
Folder 4
Section 2
Folder 5	A		A	A
Folder 6	A	A	A	A	A	A
Folder 7	A
Folder 8				A
Section 3
Folder 9
Folder 10					A
Folder 11	A

Sheet 3:

		Roles	Folder permissions
Employee	1	Product Development Design R & D Deployment IT
Employee	2	Product Development
Employee	3	R & D
Employee	4	Product Development R & D
Employee	5	Product Development Deployment IT

I have the formula to create a list of roles, but not of folders:
Cell C2: =TEXTJOIN(CHAR(10), TRUE, if(Roles!C3 ="X", Roles!$C$2, ""), if(Roles!D3="X", Roles!$D$2,""), if(Roles!E3="X", Roles!$E$2,""), if(Roles!F3="X", Roles!$F$2,""), if(Roles!G3="X", Roles!$G$2,""), if(Roles!H3="X", Roles!$H$2,""), if(Roles!I3="X", Roles!$I$2,""), if(Roles!J3="X", Roles!$J$2,""),if(Roles!K3="X", Roles!$K$2,""), if(Roles!L3="X", Roles!$L$2,""), if(Roles!M3="X", Roles!$M$2,""), if(Roles!N3="X", Roles!$N$2,""), if(Roles!O3="X", Roles!$O$2,""), if(Roles!P3="X", Roles!$P$2,""), if(Roles!Q3="X", Roles!$Q$2,""), if(Roles!R3="X", Roles!$R$2,""), if(Roles!S3="X", Roles!$S$2,""), if(Roles!T3="X", Roles!$T$2,""), if(Roles!U3="X", Roles!$U$2,""), if(Roles!V3="X", Roles!$V$2,""), if(Roles!W3="X", Roles!$W$2,""), if(Roles!X3="X", Roles!$X$2,""), if(Roles!Y3="X", Roles!$Y$2,""), if(Roles!Z3="X", Roles!$Z$2,""))

 

[eeb]

Have you changed the ranges & sheet names to refer to your data?

Yes

 

[Fluff]

In that case can you post the amended formula

 

[eeb]

In that case can you post the amended formula

Cell E3=TEXTJOIN(CHAR(10),1,IF(Roles!$C3="X",IF(Permissions!$C$2:$C$22="A",Permissions!$B$2:$B$22,""),""),IF(Roles!$D3="X",IF(Permissions!$D$2:$D$22="A", Permissions!$B$2:$B$22,""),""),IF(Roles!$E3="X",IF(Permissions!$E$2:$E$22="A",Permissions!$B$2:$B$22,""),""))

Result is blank

Cell E4=TEXTJOIN(CHAR(10),1,IF(Roles!$C4="X",IF(Permissions!$C$2:$C$22="A",Permissions!$B$2:$B$22,""),""),IF(Roles!$D4="X",IF(Permissions!$D$2:$D$22="A", Permissions!$B$2:$B$22,""),""),IF(Roles!$E4="X",IF(Permissions!$E$2:$E$22="A",Permissions!$B$2:$B$22,""),""))

Result is all folders listed once

 

[Fluff]

You haven't wrapped it in array formula like I suggested.

 

[eeb]

You haven't wrapped it in array formula like I suggested.

I was getting an error message when I did that, but it works today! Thank you so much for your help.

 

[Fluff]

You're welcome & thanks for the feedback.