How to move a large formula to another workbook but link it?

[Dan the Man]

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):

Workbook=IDIP Data

Spreadsheet=Requirement Deadliines

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?

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?

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)))

Any formula or feedback suggestions would be appreciated?

Thanks in advance,

Dan

 

[ChrisJ]

Dan,

If your main objective is to overcome the slowness, I would turn automatic
calculation off (Tools, Options, Calculation).

You can certainly set up another workbook with links. You would need to link
to every cell that is used for calculation in your formula. If linked
properly, you would not need to have the source workbook open when performing
calculations in your new workbook.

 

[Dan the Man]

Thanks for the information Chris. Do you (or anyone else), know what I would
need to include in my formula to link the 2 workbooks. I've done formulas
between spreadsheets before but never between workbooks. A sample formula
using my original would be of great help.

Btw, what impact does turning off automatic calculations have on a
spreadsheet? I have always left this on and don't know the advantages or
disadvantages of doing so or turning it off?

Thanks,

Dan