Dynamic Range With Countif and Offset

Hi all.

I am hoping someone can help me with the attached problem. I analyse data for a call centre, and although the staff turnover is incredibly low for this line of work, it still causes me headaches through laborious manual updates. My goal is to be able to enter a month in a particular cell, and have the names of the staff who were present that month pre-populate. I have this working via named ranges on tab one, but unfortunately have to have a separate list for each month, which defeats the object somewhat. What I really require is some kind of dynamic named range using countif and offset. This would look up every entry of the desired month and select the name adjacent to it as being in the range. My formula for in column F on the first tab would then do the rest.

Thanks for reading and any help is, as always, massively appreciated.

Kind regards,

James

Thanks for your suggestion avk, but unfortunately if you change the month to Nov, it no longer works.

FYI - You can't reference dynamic named range via INDIRECT function.

There are several methods that can be used here.

1. Pivot Table with filter.
2. Advanced filter with small VBA
3. Formula (might not be ideal, depending on data size)

See attached for sample set up for all 3 methods.

Thanks CK76. That formula looks to be exactly what I needed. Many thanks