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?

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?

YES!!! Thank you!!
--
Please remember to indicate when the post is answered so others can benefit
from it later.


"Luke M" wrote:

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?

OK, so now my dynamic named range seems to work fine:
=offset(Plans!$Q$4,0,0,sumproduct(--(Plans!$Q$4:$Q$103<"")),1)

But when I go to another tab and use data validation to show just the
entries in that dynamic range as a drop down list, something weird happens.
The first row available has all appropriate values in the drop down. The
second row drop down erases the last available entry from my dynamic range,
and each subsequent row is missing one more list item.

So let's say my array winds up with 2 entries: Alpha and Beta. On another
tab, I want to point to that array as a dynamic range so that a drop down
list shows just those 2 entries. I assign column A to a data validation
limited to that dynamic range. In A4, both Alpha and Beta show up. In A5,
only Alpha shows up in the drop down, then in A6, nothing is available in the
drop down.

Any idea why?



"Luke M" wrote:

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?