Indirect Function for External Reference

If you're saying you want pre-existing values to persist until such time as they can be recalculated successfully then I would say, not in any practical way.

Theoretically you could use a single test cell as basis for pre-emptive IFs (with iterative/circular calculations) but it would be cumbersome at best, eg:


where A1 is a test cell reference to the remote file
(above would require Iterative Calculation be enabled)

Traditionally - if you need to link to closed files (determined dynamically) then you can't use INDIRECT - Google INDIRECT.EXT and/or Harlan Grove's PULL Function

Hi Mike

Although you cannot control directly the recalculation of a cell you can use a workaround, using the fact that you can control the recalculation of a worksheet.

In this case those values are only recalculated when you want.

If you think this is something you can use in your case, this is an example:

- add a new worksheet, that you'll calculate manually, for ex. ManCalc

- write all the formulas that you want to be recalculated manually in ManCalc and replace the original formulas with links

- set ManCalc to be calculated manually

Now you have no problem if the external files are closed, because the formulas will only recalculate when you decide.

If you have doubts, I have a more general explanation here:

http://www.mrexcel.com/forum/showthread.php?t=485882

Thanks to both for your thoughts on this. I am going to give the "mancalc" approach a try and see if I like it. Otherwise it is back to changing the links each month. Right now my users hate my solution with all the #REF's in the file unless all the supporting files are open...

A workaround to remove the #REF errors is to append an IF(ISERROR,"",yourformula)

It basically puts all cells with a ref error to blank...

I tried the same thing your originally posted ealier and it is a right pain!

I'm sure I found a way round this ages ago but its slipped my mind for now... Once I find my old spreadsheets, I'm sure it will become apparent

FWIW, I figured I should have posted a better example of my suggestion as it was not altogether clear:



In the above B1 determines whether or not the other functions need to recalculate based on the INDIRECT or persist with existing values.

Source1.xls being:


Iterative calculations are generally best avoided, however, if you can't use VBA (to control calculation at sheet level) it's a possible alternative.