Return list of header values based upon cell text entry

Andje

New Member
Joined
Sep 16, 2019
Messages
3
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.

Project 1Project 2Project 3Project 4Project 5
Employee ACreatedTrackedNot TrackedCreated
Employee BCreatedNot Tracked

<tbody>
</tbody>


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"

CreatedTrackedNot Tracked
Project 1Project 3Project 4
Project 5

<tbody>
</tbody>


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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Hi & welcome to MrExcel.
How about


Book1
ABCDEF
1Project 1Project 2Project 3Project 4Project 5
2Employee ACreatedTrackedNot TrackedCreated
3Employee BCreatedNot Tracked
4
5
6
7Employee A
8CreatedTrackedNot Tracked
9Project 1Project 3Project 4
10Project 5
Sheet1
Cell Formulas
RangeFormula
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))),"")
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top