I'm trying to link names from one sheet to another, I know how I would do that normally but I want to add in some points that need to be true to be able to be placed in that cell.

http://prntscr.com/hpj9ym
This is the part where I'd fill in the names manually

http://prntscr.com/hpjahk
This is where the name would have to be moved in to automatically,

Sheet name 1 is named "Roster"
2nd Sheet name is "Kavala"

I need a name of department "Kavala" and rank has to be Chief Inspector and if that's true then their name should be put on the 2nd sheet.