If workbook not found then give 0 value

[HaroonSid]

hi,
i am getting data from linking cell

Please Login or Register  to view this content.

there is a problem in above formula
if workbook not found its giving answer = 97

while i need 0

thank you

[FDibbins]

If it cannot find the WB, I dont see how it can give any value, it should only return a #REF error.

To get rid of that error...
=iferror(COUNTA('Z:\42766\Jan 2 Dec 2014\1.12.16\[30.6.17.XLSB]TOTAL BC VC'!$C$4:$C$100),"")

[sandy666]

Probably your excel remeber last value

external.jpg

try un-tick this option and check values again

[FDibbins]

Probably your excel remeber last value

Attachment 523819

try un-tick this option and check values again

Good point. To test, use F9 (recalc)

[sandy666]

F9 doesn't work
You need go to DATA tab , Edit links, Break a link (or change source or whatever...) and delete value after break

who said: give me a break?

[FDibbins]

F9 will cause an error to show, rather than the value

[sandy666]

I just tested, linked cell, it shows value, eg. 88. saved my workbook, deleted external source, hit F9 and 88 is visible now like before, becaue I've ticked option like above.
If it will be un-ticked, we will see error , probably but I am too lazy to check it again

btw. office track connected files, if I move one file to another place path in link will change to be correct, (probably) especially if both of files are open when "external" is moved.

That is why we've values in OPs attachment without external file.