Pulling Data Based On Skill Number

[tomass1984]

Hi all, I have configured the below S/s -

I would like another sheet so i could identify a users training needs based on their skill level, so if user 1 has a skill of 2 or below assigned to Task 9 in the above S/s ,this would automatically populate another sheet like the below -

So you can see where the user needs to concentrate, please can you advise on the best approach?

 

[Jeffrey Mahoney]

Here is my first take on the problem

Book2
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
24	Team Member	Job Role	Task 1	Task 2	Task 3	Task 4	Task 5	Task 6	Task 7	Task 8	Task 9	Task 10	Task 11	Task 12	Task 13	Task 14	Task 15
25	User 1	Test Team	3	x	4	4	2				3	2	2	4
26	User 2	Test Team	3	4		4	1	x	x	x	1	1	4	4	x
27	User 3	Test Team	3		x				3			2	3	4		1
28	User 4	Test Team	3	4	2			4						4	1
29	User 5	Test Team	3			3	4		2	2	4	4	4	4	4
Sheet1

Book2
	A	B	C	D	E
1	User 1	User 2	User 3	User 4	User 5
2	Task 5	Task 3	Task 2	Task 3	Task 2
3	Task 6	Task 5	Task 4	Task 4	Task 3
4	Task 7	Task 9	Task 5	Task 5	Task 6
5	Task 8	Task 10	Task 6	Task 7	Task 7
6	Task 10	Task 14	Task 8	Task 8	Task 8
7	Task 11	Task 15	Task 9	Task 9	Task 14
8	Task 13		Task 10	Task 10	Task 15
9	Task 14		Task 13	Task 11
10	Task 15		Task 14	Task 13
11			Task 15	Task 14
12				Task 15
Sheet2

Cell Formulas
Range	Formula
A2:A10,E2:E8,D2:D12,C2:C11,B2:B7	A2	=LET(Ary,INDEX(Sheet1!$F$25:$T$29,MATCH(Sheet2!A$1,Sheet1!$D$25:$D$29,0),),TRANSPOSE(FILTER(Sheet1!$F$24:$T$24,Ary<3,"")))
Dynamic array formulas.

 

[ExceLoki]

OP is using 2010, they wont have LET, TRANSPOSE, or FILTER
but nice solution

 

[Fluff]

Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Jeffrey Mahoney]

OP is using 2010, they wont have LET, TRANSPOSE, or FILTER
but nice solution

Bummer