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,""))

 

[Fluff]

What version of Excel are you using, your profile shows 2016, but TextJoin is not available in that version?

 

[eeb]

What version of Excel are you using, your profile shows 2016, but TextJoin is not available in that version?

I have been using Google Sheets to collaborate - but I do use Excel 2016 on MacOS as well if there is an alternative

 

[Fluff]

Ok, I've put both tables on ths same sheet for convenience

+Fluff 1.xlsm
	A	B	C	D	E	F	G
1
2	First	Last	Product Development	Design	R & D	Deployment	IT
3	Employee	1	X	X	X	X	X
4	Employee	2	X
5	Employee	3			X
6	Employee	4	X		X
7	Employee	5	X			X	X
8
9
10
11	Permissions	Product Development	Design	R & D	Deployment	IT	Quality Assurance
12	Section 1
13	Folder 1
14	Folder 2		A				A
15	Folder 3	A	A	A		A	A
16	Folder 4
17	Section 2
18	Folder 5	A		A	A
19	Folder 6	A	A	A	A	A	A
20	Folder 7	A
21	Folder 8				A
22	Section 3
23	Folder 9
24	Folder 10					A
25	Folder 11	A
Sheet1

You could then use

=TEXTJOIN(CHAR(10),1,IF(Sheet1!C3="X",IF(Sheet1!$B$12:$B$25="A",Sheet1!$A$12:$A$25,""),""),IF(Sheet1!D3="X",IF(Sheet1!$C$12:$C$25="A",Sheet1!$A$12:$A$25,""),""))

Although some of the folders will be listed multiple times.

 

[eeb]

I am not getting any result using the nested IF functions

 

[Fluff]

You may need to wrap-it in arrayformula.

 

[eeb]

Thanks, but does not work. I get either a blank value, or a list of every folder once.

 

[Fluff]

In what way?

 

[eeb]

In what way?

I get either a blank value, or a list of every folder once.

 

[Fluff]

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