EXCEL FORMULA NOT UPDATED WHEN LINKED FILE IS CLOSED

[anna99]

Hi All,

Could you please help me with the issue below
I did some excel formulas which linked to an external excel file. when both files are open the formula will be auto adjusted (example if I insert a column, the formulas will be auto shifted to next column accordingly). however, when i have only the linked filed opened if i insert more columns, the other files that i have formulas won't be auto adjusted.

Thanks for your help!

 

[jasonb75]

As far as I'm aware that is standard behaviour for formulas with an external source.

The only workaround that comes to mind would be to create named ranges in the source workbook, then refer to those names in your formulas instead of the actual ranges.

 

[anna99]

As far as I'm aware that is standard behaviour for formulas with an external source.

The only workaround that comes to mind would be to create named ranges in the source workbook, then refer to those names in your formulas instead of the actual ranges.

Thank you Jason, another issue is formula is not working (shown as #value!) if the other file is closed, do you know how to fix this?

 

[jasonb75]

It depends on the formula. There are certain functions that can only be used with an open workbook.

INDIRECT, SUMIF(s), COUNTIF(a), AVERAGIF(s), etc. are the most common ones. It also applies to office 365 dynamic arrays, although without further testing I'm not sure if that applies to all arrays or only specific functions.