Reference to external closed workbook using "sum" and "if"

[Generally_confused]

I am trying to use a sum and if function to reference an external workbook when the external workbook is closed. The formula that I am using is:

=SUM(IF(Forecasts_2022.xlsm!Forecast14[Resource Name]=B15,Forecasts_2022.xlsm!Forecast14[Feb-22]))

This formula references an external table and if there is a name match to the external table then it will sum up the values associated with that name. For example, if the name in cell B15="Joe" and the external workbook's "Resource Name" column also has "Joe" in it, then the numbers associated with Joe will be added. The issue is that this formula worked a few days ago when the external workbook is closed but it now no longer works. Can anyone help? I also used the same formula structure to reference a different closed workbook and that works but I am not sure why the reference to this Forecasts closed workbook is not. Thanks!

 

[Coding4Fun]

I don't know the exact answer as to why but update it to be the full path to the closed workbook and it should work. If you open both workbooks at the same time I believe it updates the formula to a short path and no longer links to the external book but not 100% on that.

 

[Generally_confused]

I am having trouble referencing the full path. I have added the full path to the formula but when I close the external workbook the #REF error pops up again.

 

[Coding4Fun]

Lets say I have a file called MyExcelFile.xlsx in my downloads folder and I want to reference cell A1 from Sheet1

='C:\Users\Username\Downloads\[MyExcelFile.xlsx]Sheet1'!$A$1

 

[Fluff]

As far as I know you cannot use structured references if you want the formula to work with a closed workbook, you will need to use normal ranges.

 

[Generally_confused]

I am having trouble referencing the full path. I have added the full path to the formula but when I close the external workbook the #REF error pops up again.

As far as I know you cannot use structured references if you want the formula to work with a closed workbook, you will need to use normal ranges.

Thank you! what you said is true: Links to data tables residing in external files display #REF!

 

[Fluff]

Glad to help & thanks for the feedback.