Return list of header values based upon cell text entry

[Andje]

Hello,
First, I cannot use VBA for this; I need the spreadsheet to function within a sharepoint environment that doesn’t accept macro enabled worksheets. I am also very new to excel, but trying to progress rapidly.

I am trying to create a single sheet within my dataset to return individual employee reports. My dataset has employee names in column A1 and project names running across the top row. Within each corresponding cell employees are listed as either “tracked”, “not tracked”, “created” or blank if they are not on a project. There are hidden cells between the projects that list other info not relevant for this problem, but an ISBLANK will not work here.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Project 1[/TD]
[TD]Project 2[/TD]
[TD]Project 3[/TD]
[TD]Project 4[/TD]
[TD]Project 5[/TD]
[/TR]
[TR]
[TD]Employee A[/TD]
[TD]Created[/TD]
[TD][/TD]
[TD]Tracked[/TD]
[TD]Not Tracked[/TD]
[TD]Created[/TD]
[/TR]
[TR]
[TD]Employee B[/TD]
[TD][/TD]
[TD]Created[/TD]
[TD][/TD]
[TD][/TD]
[TD]Not Tracked[/TD]
[/TR]
</tbody>[/TABLE]


I am trying to return a list of the projects an individual employee is on and their status when the employees name is selected from a drop down list. It should look like this.

"Employee A"

[TABLE="width: 500"]
<tbody>[TR]
[TD]Created[/TD]
[TD]Tracked[/TD]
[TD]Not Tracked[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]Project 3[/TD]
[TD]Project 4[/TD]
[/TR]
[TR]
[TD]Project 5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I have been trying nested IF statements to find the instances of specific text in cells, and then a SMALL function to output those as a list of column numbers. My intention would be to then use those to reference the column header and return it instead of the column number. However, I have no idea if this is the easiest or fastest way to go, and would appreciate any help available.

Thanks,

Andje

 

[Fluff]

Hi & welcome to MrExcel.
How about

Book1
	A	B	C	D	E	F
1		Project 1	Project 2	Project 3	Project 4	Project 5
2	Employee A	Created		Tracked	Not Tracked	Created
3	Employee B		Created			Not Tracked
4
5
6
7	Employee A
8	Created	Tracked	Not Tracked
9	Project 1	Project 3	Project 4
10	Project 5
Sheet1

Cell Formulas
Range	Formula
A9	=IFERROR(INDEX($B$1:$F$1,AGGREGATE(15,6,(COLUMN($B$1:$F$1)-COLUMN($B$2)+1)/(($A$2:$A$3=$A$7)*($B$2:$F$3=A$8)),ROWS($A$1:$A1))),"")

 

[Andje]

Amazing, Thanks.
That is working fine, but to be honest I am also trying to really understand what I am doing, and I have found learning from this site is occasionally made more difficult by a working formula but a lack of explanation. Could you explain the steps here in just a very basic rundown?

Thanks so much!

Andje

 

[Fluff]

I find the easiest way to see what a formula is doing is to use the "Evaluate Formula" feature on the Formulas tab. You can step through the calculation chain to see what each step is doing.
Give that a go & if you still have any questions, just ask