Create the List of Employee

[Baldev Kumar]

I want to create the list of employee in another workbook (as in sheet "Store") from existing sheet "List of Employee". Is it possible if i change the cell "b2" in store sheet HO to MZRC or any other as in dept column in sheet "List of Employee", total list of employee under this dept start appearing in the store sheet. if resolved this will be great help.

[Ace_XL]

In Cell A4

=IF(ROWS(A$1:A1)>COUNTIF('List of Employee'!$C$2:$C$34,$B$1),"",INDEX('List of Employee'!$A$2:$A$34,SMALL(INDEX(('List of Employee'!$C$2:$C$34=$B$1)*(ROW('List of Employee'!$C$2:$C$34)-ROW($B$2)+1),),COUNTIF('List of Employee'!$C$2:$C$34,"<>"&$B$1)+ROWS(A$1:A1))))

Copy down as far as required

In B4
=IF($A4="","",INDEX('List of Employee'!B:B,MATCH($A4,'List of Employee'!$A:$A,0)))

Copy down and across as far as required

[Baldev Kumar]

Awesome, thank you very much

[Pete_UK]

See attached file - I've put a drop-down in cell B1 (yellow) so you can easily select the dept.

Hope this helps.

Pete

[xlbiznes]

Hi,
One more ,this is a vba based solution.