Excel links only updates when source workbook is open

Hi All,

I built a template which pulls data from the source file using formulas. the problem is formulas doesn't give any error it updates the right value
when the source workbook is open.

I Thought problem could be with the formula and tried just Source A1=destination A1. it also updates only if the source file is open. when the source
file is closed it shows the last captured value.

The source file is on the (Work) Network Drive and is accessible to few people. my template is password protected (tabs) so formulas cannot be deleted.

Can someone help me with this problem.

Thanks.