can't open pivottable source file

Hello, I am trying to create workbooks with a few pivots (destination) pulling data from another file (source). And I want to be able to refresh the destination pivots without having the source file open.

It works *perfectly* from one data file; I did it by copying the tab into another file via right click/move or copy. The destination pivot table refreshes, the source is closed.

However from a totally different source data file to a wholly different source file, it is not working. If the source file is closed, I get the Can't open pivottable source file: ... error message. The destination pivot(s) will refresh only if I have the source file open.

I didn't create either source file. We're not using power pivot or query. We not using data model. I've messed around with checking the connections and Get Data/From File... and it's still happening!

(I'm not trying to use data from both source files in one pivot in the destination or anything like that. I don't need them to refresh automatically either.)

I can't choose Use an external data source in the Change PivotTable Data Source box. Yes or no -- is the file path supposed to be in the source table/range entry or not? I've googled a bunch and searched here but can't find anything totally relevant.

The 2nd source data is much more complicated and the data uses formulas within; the pivots in that same workbook work just fine.

Thanks in advance!