Help needed.
I posted this question earlier but this is a simplified version of it.
In column A I have employees' names, in column B I have Team Leaers' names. Employee names will be unique in each cell but the team leaders names will be repeated several times. something like Employee TL John Robert Kelly Robert Michael Robert Gary Daniel Jerry Daniel Jim Robert Brian Robert Richie Kevin Pete Kevin Alex Daniel Now if I enter the Team Leader's name in another sheet's column A and drag it down, I want the names of the employees (only the ones in his team) to return in column B. Possible? Thanks. |
Help needed.
In Sheet 2
=IF(ISERR(SMALL(IF(TL=Sheet2!$A$2,ROW(INDIRECT("1: "&ROWS(TL)))),ROWS($1:1))),"",INDEX(Employee,SMALL (IF(TL=Sheet2!$A$2,ROW(INDIRECT("1:"&ROWS(TL)))),R OWS($1:1)))) or =IF(COUNTIF(TL,$A$2)=ROWS($1:1),INDEX(Employee,SM ALL(IF(TL=$A$2,ROW(TL)-MIN(ROW(TL))+1),ROWS($1:1))),"") Both of formulas above are required with ctrl+shift+enter, not just enter copy down as far as needed. "Gary" wrote: I posted this question earlier but this is a simplified version of it. In column A I have employees' names, in column B I have Team Leaers' names. Employee names will be unique in each cell but the team leaders names will be repeated several times. something like Employee TL John Robert Kelly Robert Michael Robert Gary Daniel Jerry Daniel Jim Robert Brian Robert Richie Kevin Pete Kevin Alex Daniel Now if I enter the Team Leader's name in another sheet's column A and drag it down, I want the names of the employees (only the ones in his team) to return in column B. Possible? Thanks. |
Help needed.
Try this.
Sheet1 A2:A11 = employee names Sheet1 B2:B11 = team leader names Sheet2 A1 = any one of the team leaders names Enter this array formula** in Sheet2 A2: =IF(ROWS(A$2:A2)<=COUNTIF(Sheet1!B$2:B$11,A$1),IND EX(Sheet1!A$2:A$11,SMALL(IF(Sheet1!B$2:B$11=A$1,RO W(Sheet1!A$2:A$11)-MIN(ROW(Sheet1!A$2:A$11))+1),ROWS(A$2:A2))),"") Copy down until you get blanks. You need to copy to enough cells that covers the max number of employees for any single leader. For example, in your sample Robert is listed the most, 5 times, so you need to copy the formula to at least 5 cells. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Gary" wrote in message ... I posted this question earlier but this is a simplified version of it. In column A I have employees' names, in column B I have Team Leaers' names. Employee names will be unique in each cell but the team leaders names will be repeated several times. something like Employee TL John Robert Kelly Robert Michael Robert Gary Daniel Jerry Daniel Jim Robert Brian Robert Richie Kevin Pete Kevin Alex Daniel Now if I enter the Team Leader's name in another sheet's column A and drag it down, I want the names of the employees (only the ones in his team) to return in column B. Possible? Thanks. |
All times are GMT +1. The time now is 05:33 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com