Link not updating

[Stephen Simons]

I have a formula that links to an external workbook. This formula works, updates and gives a correct answer if the
external workbook is open, but if it is closed ithe formula gives a #value! error.

I have checked that the appropriate options on the Tools/Options/Calculation are ticked (external refs; iteration etc);

I have other, very similar formulae in the workbook that reference other external sheets, and the same does NOT happen
with them.

I have also tried copying and pasting the source data to a new workbook and chaning the links source, but it styill
gives the error.

Any help much appreciated.

TIA

Steve
remove "_ILY_" to email me

email to (e-mail address removed)

 

[Dave Peterson]

If your formula built that link using =indirect(), then that's the problem.
=indirect() won't work with closed workbooks.

If your formula was something else, you may want to post it so that you'll get a
better response.

 

[Stephen Simons]

Hi Dave

Thanks for that. It's a countif with a nested vlookup.

Now you've mentioned that indirect doesn't work with closed workbooks, I've experimented with countif and it appears it
also doesn't work with closed workbooks.

Can you think of a workaround?

Steve

If your formula built that link using =indirect(), then that's the problem.
=indirect() won't work with closed workbooks.

If your formula was something else, you may want to post it so that you'll get a
better response.

remove "_ILY_" to email me

email to (e-mail address removed)

 

[Dave Peterson]

If the "if" part doesn't change, maybe you could have a cell in that other
workbook with your formula in it and just retrieve that.

And this array formula worked for me with a closed workbook:
=SUM(IF('C:\My Documents\excel\[book1.xls]Sheet1'!$A$1:$A$20="asdf",1,0))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

So did this non-array formula:
=SUMPRODUCT(--('C:\My Documents\excel\[book1.xls]Sheet1'!$A$1:$A$20="asdf"))

 

[Stephen Simons]

Hi Dave

Many, many thanks for this . . . it's all sorted out fine now with the array formula option.

Steve

If the "if" part doesn't change, maybe you could have a cell in that other
workbook with your formula in it and just retrieve that.

And this array formula worked for me with a closed workbook:
=SUM(IF('C:\My Documents\excel\[book1.xls]Sheet1'!$A$1:$A$20="asdf",1,0))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

So did this non-array formula:
=SUMPRODUCT(--('C:\My Documents\excel\[book1.xls]Sheet1'!$A$1:$A$20="asdf"))

Hi Dave

Thanks for that. It's a countif with a nested vlookup.

Now you've mentioned that indirect doesn't work with closed workbooks, I've experimented with countif and it appears it
also doesn't work with closed workbooks.

Can you think of a workaround?

Steve



remove "_ILY_" to email me

email to (e-mail address removed)

remove "_ILY_" to email me

email to (e-mail address removed)