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

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,792
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style=";">Project 1</td><td style=";">Project 2</td><td style=";">Project 3</td><td style=";">Project 4</td><td style=";">Project 5</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Employee A</td><td style=";">Created</td><td style="text-align: right;;"></td><td style=";">Tracked</td><td style=";">Not Tracked</td><td style=";">Created</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Employee B</td><td style="text-align: right;;"></td><td style=";">Created</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Not Tracked</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Employee A</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Created</td><td style=";">Tracked</td><td style=";">Not Tracked</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Project 1</td><td style=";">Project 3</td><td style=";">Project 4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">Project 5</td><td style=";"></td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A9</th><td style="text-align:left">=IFERROR(<font color="Blue">INDEX(<font color="Red">$B$1:$F$1,AGGREGATE(<font color="Green">15,6,(<font color="Purple">COLUMN(<font color="Teal">$B$1:$F$1</font>)-COLUMN(<font color="Teal">$B$2</font>)+1</font>)/(<font color="Purple">(<font color="Teal">$A$2:$A$3=$A$7</font>)*(<font color="Teal">$B$2:$F$3=A$8</font>)</font>),ROWS(<font color="Purple">$A$1:$A1</font>)</font>)</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />
 

Andje

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,792
Office Version
  1. 365
Platform
  1. Windows
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
 

Forum statistics

Threads
1,147,821
Messages
5,743,393
Members
423,792
Latest member
travisds

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
Top