Build a Dynamic Named Range from criteria.

Hello Forum.

I have some raw data, and in that data are 2 columns. They are Manager and Role. I am trying to create 2 lists, that correspond to their role. I can create a dynamic named range from the column no problem using this formula:

Formula:

Please Login or Register  to view this content.

Now, what I need to be able to do is, add a criteria option to the formula. So build the list off of column B, but only if their role (in column C) is Perm, or Temp.

Please see the attached spreadsheet.

Thank you for the quick reply. I see that your macro works in the example that I provided, but I would have no idea how to convert that to my workbook. Could you maybe add comments to the macro that would allow me to understand it, and adapt it for use in my workbook.

I have thought about a pivot table approach, but I wanted to stay way from them

I apologize that I didn't specify that I wanted to stay away from a pivot table. The reason is this; There is a specific area of the report that this list of Managers needs to go, and a pivot table would change the cells around in such a way that it would mess up the formatting of the report. Also, I am designing this report, but I will not be maintaing it, so I wanted to make it as simple as possible, and something as little as refreshing the table could cause problems.