How to apply a formula a certain number of times based on another cell

[Grace1918]

Hey there,

I have two tables that contain all of our companies employees and am looking to count the number of times a certain position occurs in one table and put the adjacent name into a row in another column. I am currently using

=IFERROR(INDEX($A:$A,SMALL(IF("Senior Project Director"=$B:$B,ROW($B:$B),""),ROW()-ROW(F2))),"")

and was hoping that someone would know of a way to have a macro drag the formula down the number of cells that certain position occurs.

I have been using Countif to count the positions but I don't know if that applies to VBA. We are always adding new employees to the list so that's why I was hoping it could be dynamic instead of having a long array.

Thanks in advance to anyone who has any ideas.

 

[Fluff]

Ok, you cannot use indirect with dynamic named ranges.
Create a named range called Senior_Project_Director that refers to F3:F16 & use this in the Data validation
=OFFSET(INDIRECT(SUBSTITUTE($F2," ","_")),0,0, COUNTIF(INDIRECT(SUBSTITUTE($F2, " ","_")),"?*"),1)