Refresh Pivots from closed external source

I am at a loss for what I am missing and why this isn't working. I created a pivot table and when I selected the data I selected an external workbook that is saved to our network drive. When the file is open everything works perfectly. When the source data file is closed I get "The PivotTable field name is not valid. To create a PivotTale report, you must use data that is organized as a list with labeled columns. If you are changing the name of a PivotTable field, you must type a new name for the field." It won't let me refresh the pivot table unless the source data is open.

Is this normal? What am I missing? I am assuming it has something to do with being on a secure network and needing certain permissions to the drive? There is another pivot table that was already created and the data source for that pivot table is on a tab within the workbook. In that scenario I copy the pivot table to an external spreadsheet and it works just fine. Probably because the original pivot table and data are in the same workbook.

I tried doing this with connection to a flat .xls file and that doesn't work either. Any help or reading anyone could recommend to maybe resolve the issue? I am trying to link to a file that updates from our system daily instead of copy and pasting this into a data tab.

How did you select the data in the external workbook? Did you use a data query, or simply select a range while that workbook was open?

I simply selected the range while the workbook was open.