Indirect Function for External Reference

[MikeDBMan]

I am using this formula instead of a linked external spreadsheet:

=INDIRECT("'[" & $A$1 &" "& $A9&".xlsx]Cost Summary'!" & C$1 & "$" &$B$1)

The values in A1 and A9 contain values that change that help build the name of the spreadsheet. If the external reference is open, it auto-updates. However if it is not open, the formula evaluates to "#REF!". Is there a way to have the formula NOT calculate unless it returns no error? Thanks for the help!

The reason I am doing this is to avoid having to update the external links each month as the file names do change monthly.

 

[DonkeyOte]

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:

B1:
=IF(ISERR(A1),B1,INDIRECT(....))

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

 

[pgc01]

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

 

[MikeDBMan]

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

 

[Stu Dapples]

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

 

[DonkeyOte]

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

Excel Workbook
	A	B
1	'[source1.xls]sheet1'!	$A$1
2
3	A2	$A$2
4	B10	$B$10
5	C12	$C$12
6	D2	$D$2
Sheet1

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:

$A$1	$B$1	$C$1	$D$1	$E$1
$A$2	$B$2	$C$2	$D$2	$E$2
$A$3	$B$3	$C$3	$D$3	$E$3
$A$4	$B$4	$C$4	$D$4	$E$4
$A$5	$B$5	$C$5	$D$5	$E$5
$A$6	$B$6	$C$6	$D$6	$E$6
$A$7	$B$7	$C$7	$D$7	$E$7
$A$8	$B$8	$C$8	$D$8	$E$8
$A$9	$B$9	$C$9	$D$9	$E$9
$A$10	$B$10	$C$10	$D$10	$E$10
$A$11	$B$11	$C$11	$D$11	$E$11
$A$12	$B$12	$C$12	$D$12	$E$12

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