Return list based on Cell Value

[stanleytheyak]

I've looked through the forum, but can't find anything similar. I have a sheet to calculate quarterly incentives. Based on criteria, it tells me if the particular individual is eligible or not. I'm trying to create a summary page that will give me a quick overview without all the underlying data. Here is the underlying data:

<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Calibri,Arial; font-size: 11pt;" border="1" cellpadding="0" cellspacing="0"><tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"><td>A</td> <td>B</td> <td>C</td> <td>D</td> <td>E</td> <td>F</td> <td>G</td> <td>H</td> <td>I</td> <td>J</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">1</td> <td>Specialist</td> <td>Survey Score</td> <td>Tickets Opened</td> <td>Calls Answered</td> <td>Chats Taken</td> <td>Ticket %</td> <td>% Time Available</td> <td>Action Plan</td> <td>Quality Score</td> <td>Eligible?</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">2</td> <td style="font-family: ARIAL; font-size: 10pt;">Spec 1</td> <td style="text-align: right;">3.90</td> <td style="text-align: right;">836</td> <td style="text-align: right;">744</td> <td style="text-align: right;">237</td> <td style="text-align: right;">85.22%</td> <td style="text-align: right;">86.21%</td> <td>No</td> <td style="text-align: right;">85%</td> <td>No</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">3</td> <td style="font-family: ARIAL; font-size: 10pt;">Spec 2</td> <td style="text-align: right;">3.67</td> <td style="text-align: right;">388</td> <td style="text-align: right;">0</td> <td style="text-align: right;">1068</td> <td style="text-align: right;">36.33%</td> <td style="text-align: right;">0.00%</td> <td>No</td> <td style="text-align: right;">85%</td> <td>Yes</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">4</td> <td style="font-family: ARIAL; font-size: 10pt;">Spec 3</td> <td style="text-align: right;">3.82</td> <td style="text-align: right;">776</td> <td style="text-align: right;">1085</td> <td> </td> <td style="text-align: right;">71.52%</td> <td style="text-align: right;">69.85%</td> <td>No</td> <td style="text-align: right;">85%</td> <td>Yes</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">5</td> <td style="font-family: ARIAL; font-size: 10pt;">Spec 4</td> <td style="text-align: right;">3.88</td> <td style="text-align: right;">579</td> <td style="text-align: right;">689</td> <td> </td> <td style="text-align: right;">84.03%</td> <td style="text-align: right;">79.16%</td> <td>No</td> <td style="text-align: right;">85%</td> <td>Yes</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">6</td> <td style="font-family: ARIAL; font-size: 10pt;">Spec 5</td> <td style="text-align: right;">3.93</td> <td style="text-align: right;">770</td> <td style="text-align: right;">623</td> <td style="text-align: right;">337</td> <td style="text-align: right;">80.21%</td> <td style="text-align: right;">91.37%</td> <td>No</td> <td style="text-align: right;">85%</td> <td>No</td></tr></tbody></table>
What I'd like to have happen on the summary page is for the Specialists who are eligible to appear in the list. So in this case, my summary would look like this:

<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Calibri,Arial; font-size: 11pt;" border="1" cellpadding="0" cellspacing="0"><tbody><tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"> <td> </td> <td>B</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">1</td> <td style="font-weight: bold; text-decoration: underline;">Specialists Eligible</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">2</td> <td>Spec 2</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">3</td> <td>Spec 3</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">4</td> <td>Spec 4</td></tr></tbody></table>
Any help is appreciated.

 

[VaraK]

Hi

Make a pivot table based on your underlying data. Use the field "Eligible?" as the report filter.

Drag the Field "Specialist" into ROw labels and you are done. Whenever the underlying data is updated you just need to refresh the pivot.

Hope this helps.

 

[schielrn]

This should work:

http://www.mrexcel.com/forum/showthread.php?t=390310

Hope that helps.

 

[stanleytheyak]

schielrn, as usual, you've hit the nail on the head. Excellent reference! Thank you so much!