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:

fill in 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.


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.