Trying to reference data from another sheet to another

Question

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.

This is the part where I'd fill in the names manually:

This is where the name would have to be moved in to automatically,

Sheet name 1 is named "Roster". Second 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.

Answer 1

The query function does approximately this.

=query(Roster!A:D, "select B, A where D = 'Kavala' order by A", 1) 

selects the rows where department is Kavala and takes the entries from B (name) and A (title), sorting by title alphabetically.

To avoid getting a header row from the query, use

=query(Roster!A:D, "select B, A where D = 'Kavala' order by A label B '', A ''", 1) 

The only issue remaining is that you want some custom order by title, even with separating rows. The query won't be able to do that but you can use multiple queries for specific ranks:

=query(Roster!A:D, "select B, A where D = 'Kavala' and B = 'Inspector' order by A", 1) 

Ultimately there will be a conflict between the desire for automatic transfer of data from one sheet to another, and the manual formatting of the second sheet.