Need a formula to generate a list just type department name

[AbidBahawalpur1986]

need a formula to generate list from my source data just type the name of department in 2nd sheet and all faculty members working in the said departments show automatically. i also have a three campuses in the list. list show in order of designation like 1.Professor, 2.Associate Professor, 3.Assistant Professor, 4.Lecturer.
also need a formula to generate retired faculty members list retired during financial yeartemplate for list of Department & Retired.xlsx

[AliGW]

Welcome to the forum.

With the department name ONLY in G1:

=CHOOSECOLS(FILTER('Numbers of Teachers Complete'!A5:R22,'Numbers of Teachers Complete'!G5:G22=Department!G1),1,3,4,8,9,11,12,14,15,16,18)

Retired:

=CHOOSECOLS(FILTER('Numbers of Teachers Complete'!A5:R22,('Numbers of Teachers Complete'!R5:R22>=P5)*('Numbers of Teachers Complete'!R5:R22<=P6)),1,3,4,8,9,11,12,14,15,16,18)

[AbidBahawalpur1986]

Thanks you so much.
So nice of you

One thing I forgot to type their is a condition in column if joining status faculty member is active then show in department list

[AliGW]

Change it to this:

=CHOOSECOLS(FILTER('Numbers of Teachers Complete'!A5:R22,('Numbers of Teachers Complete'!G5:G22=Department!G1)*('Numbers of Teachers Complete'!J5:J22="Active")),1,3,4,8,9,11,12,14,15,16,18)

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, remember that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

[AbidBahawalpur1986]

Can the serial number (1,2,3,....) be autmated?

[AliGW]

Headers could be used, but it would mean a much longer and clumsy formula. Why - do the columns move? If not, then there is no issue.

This thread should be marked as SOLVED, as the issue raised at the outset has been dealt with.

[AbidBahawalpur1986]

Thank you for understanding my query and helping. It has helped me to get the results!

[AbidBahawalpur1986]

Thank you for understanding my query and helping. It has helped me to get the results!