"Dan the Man" wrote...
The "array" formula you see below works great in terms of calculating
what I need, however it also slows things down significantly which is
understandable. I've also temporarily removed the formula due to the
compromised performance problems it creates on my workbook. My question
is this? Can I create a NEW workbook, and NEW spreadsheet (suggestion
below):
....
Set up a link to the original workbook (IDIP Client Database) and
spreadsheet (New Rule Clients), and bypass my "slowness" problem by
having all the work and effort moved to a different workbook?

If this other workbook would already have done all the work and been saved
with everything just they way you need it AND you'd open it before accessing
these results in it, then, yes, it could speed things up.

I didn't know if this were possible? Is there a formula that could do
this, and if so, do I have to have my current workbook (IDIP Client
Database) OPEN in order for the formula to work and calculate appropriate
outcomes in the NEW workbook?

If recalc speed is as important as you indicate above, external references
into CLOSED workbooks would very likely be MUCH SLOWER than the array
formula you're currently using. So, yes, you'd need to open that workbook.

The current formula I am using on my "New Rule Clients" spreadsheet of
my "IDIP Client Database" Workbook is:

=IF(ISERROR(SMALL(IF(($Z$4:$Z$3500<"")
*($AH$4:$AH$3500="Send IDIP 036 to DPHS"),ROW($Z$4:$Z$3500),""),
ROW($A1))-ROW($A$4)+1),"",
INDEX(A$4:A$3500,N(SMALL(IF(($Z$4:$Z$3500<"")
*($AH$4:$AH$3500="Send IDIP 036 to DPHS"),ROW($Z$4:$Z$3500),""),
ROW($A1))-ROW($A$4)+1)))

The main problem here is calling SMALL(<huge array term,..) twice. And
SMALL isn't exactly a zippy function either. This is an instance in which
you should use two cells for each end result. That is, (using made-up cell
addresses),

X99 [array formula]:
=SMALL(IF(($Z$4:$Z$3500<"")*($AH$4:$AH$3500="Send IDIP 036 to DPHS"),
ROW($Z$4:$Z$3500),""),ROW($A1))-ROW($A$4)+1

Y99:
=IF(ISNUMBER(X99),INDEX(A$4:A$3500,X99),"")

Since these two formulas are much simpler than your original formula, they
won't take up much more memory, and they should nearly double recalc speed.

But if you're going to use two cells per each result, use them for more
efficient formulas.

X99 [array formula]:
=MATCH(1,($Z$4:$Z$3500<"")*($AH$4:$AH$3500="Send IDIP 036 to DPHS"),0)

X100 [array formula]:
=MATCH(1,INDEX($Z$4:$Z$3500,X99+1):$Z$3500<"")
*(INDEX($AH$4:$AH$3500,X99+1):$AH$3500="Send IDIP 036 to DPHS"),0)+X99

Fill X100 down as far as needed. Y99 would remain as above. Not only is
MATCH, at O(N), a decided improvement over SMALL, at O(N*log(N)) *IF* SMALL
uses a quicksort variant or O(N^2) if it uses a bubble sort or shell sort
variant, but the INDEX(a:z,LastMatchIndex+1):z is also shrinking for each
subsequent MATCH call. Faster algorithm on progressively smaller ranges
definitely won't hurt.

The downside, however, is two cells per end result. This is the age-old
speed-storage trade-off. You get to decide which is more important.