Copying value based on another cell value

[eeb]

Hello,
I am looking to consolidate a list of employees and training requirements.
I have one sheet which lists employees and what functional roles they fall under.
Another sheet lists training requirements per functional role.
I want to set up a third sheet which would consolidate training requirements per employee.

Example:
Sheet 1

	Role 1	Role 2	Role 3
Employee 1	X	X
Employee 2		X
Employee 3		X	X

Sheet 2

	Role 1	Role 2	Role 3
Training Requirement 1			X
Training Requirement 2	X	X	X
Training Requirement 3	X		X

Sheet 3

	Employee 1	Employee 2	Employee 3
Training Requirement 1
Training Requirement 2
Training Requirement 3

Thanks!

 

[JamesCanale]

I think this does it...

Mr Excel Playground 3.xlsm
	A	B	C	D	E	F	G	H	I
1		Role 1	Role 2	Role 3			Employee 1	Employee 2	Employee 3
2	Employee 1	X	X			Training Requirement 1			X
3	Employee 2		X			Training Requirement 2	X	X	X
4	Employee 3		X	X		Training Requirement 3	X		X
5
6
7		Role 1	Role 2	Role 3
8	Training Requirement 1			X
9	Training Requirement 2	X	X	X
10	Training Requirement 3	X		X
Sheet31

Cell Formulas
Range	Formula
G2:I4	G2	=IF(INDEX($B$2:$D$4,MATCH(G$1,$A$2:$A$4,0),COLUMN(B$1)-1)=INDEX($B$8:$D$10,MATCH($F2,$A$8:$A$10,0),COLUMN(B$1)-1),"X","")

 

[eeb]

COLUMN(B$1)-1

Thank you! What would you do in the case where the requirements didn't match up this way (A7:D10 does not happen to match F1:I4) ? The column reference is not working for me.

 

[JamesCanale]

I'm not clear on what you mean.

 

[eeb]

I'm not clear on what you mean.

Sorry - let me rephrase - Would this same formula work for 20 different employees with 20 different roles and 20 different requirements?

 

[JamesCanale]

No. The formula ranges would have to change. But nothing else I think - if the overall structure is the same. Every range would have to change to the new range. You might use named ranges to make it easier to read and to update.

 

[eeb]

I believe it is only copying the column over- see example here using the formula

	Role 1	Role 2	Role 3			Employee 1	Employee 2	Employee 3
Employee 1	X	X			Training Requirement 1			X
Employee 2		X			Training Requirement 2		X	X
Employee 3	X		X		Training Requirement 3			X
	Role 1	Role 2	Role 3		Training Requirement 4	X
Training Requirement 1			X
Training Requirement 2		X	X
Training Requirement 3			X
Training Requirement 4	X

G2 =IF(INDEX($B$2:$D$4,MATCH(G$1,$A$2:$A$4,0),COLUMN(B$1)-1)=INDEX($B$6:$D$9,MATCH($F2,$A$6:$A$9,0),COLUMN(B$1)-1),"X","")