I believe this will work, I tried to setup a duplicate workbook at you
described:

=IF(COUNTIF(Termd!$E$4:$E$103,"Involuntary")=ROW( )-ROW($A$4)+1,INDIRECT("Termd!"&ADDRESS(LARGE((Termd !$E$4:$E$103="Involuntary")*ROW(Termd!$E$4:$E$103) ,COUNTIF(Termd!$E$4:$E$103,"Involuntary")-ROW()+4),COLUMN()-COLUMN($Q$4)+1)),"")
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"KC Rippstein" wrote:

Bernie provided a tip in another post exactly like my situation, and I tried
to apply his formula but cannot get it to work.

I have a list of people in Column A on a sheet called Termd.
In column E of that same sheet is a status of Voluntary or Involuntary.

On a sheet called Plans, I need to setup a list of just the Involuntary
people in column Q without having to apply a filter or use any macros, so an
array is the way to go. My goal is to then create a dynamic named range from
this list so that on another sheet I can have a drop-down data validation
list of just the Involuntary people.

Since my data on Termd begins in row 4, I set up the Involuntary list
starting at Plans!Q4.
Here's my failed attempt which results in an answer of zero.

=IF(COUNTA(Termd!$A$4:$E$103)=ROW()-ROW($A$4)+1, INDIRECT("Termd!" &
ADDRESS(LARGE((Termd!$E$4:$E$103<"Voluntary")*ROW (Termd!$E$4:$E$103),
COUNTA(Termd!$E$4:$E$103)+ROW($A$4)-ROW()), COLUMN()-COLUMN($Q$4)+1)),"")

Help?