Refer to specific cell in external file

INDIRECT works, but the external file must be open.

One way to do this with VBA (if you do not want to have to open the external file) is to use VBA to build your "want it to be" formula.

I have implemented the code in your file, and exercised on the rows where you have formulas. But since I don't have your other file I can't test it.

Whenever a value in column C or column D changes, the formula in F is updated.

Another option may be to install the Morefunc add-in which allows using INDIRECT on closed workbooks...
http://www.ashishmathur.com/tag/morefunc/

As long as you got where you wanted to be, and thanks for the feedback

Originally Posted by esbencito

However, my file has some heavy calculations, hence a "Worksheet_Change" code will trigger quite some lag due to recalculating.
Is there also a way to re-write the code to have it run once when a button is clicked and then pasting it as values after?

Yes but it won't make it any faster. The recalculating is triggered by adding the formula. By then the lag will have occurred and nothing will be saved by converting the formula to a value.

One other option is to have the code open the other file, copy the value, then paste it in.

The only other option I can think of (without using add-ins) is for the code to use ADO to read the other file without opening it, which treats it like a database.

ETA: Function INDIRECT.EXT from the Morefunc package does one of those two things but I'm not sure which.

Bear in mind that with the formula approach I provided, the value will always match the value in the external file. These other two options (plus your suggestion) create a static value, and if that value changes in the other file it will never be updated in your file. I don't know what your requirements are.