Instead of INDIRECT() need to have INDEX and MATCH

You need full path to the file. But that should automatically update after you close the referenced workbook.

Ex: 'C:\Myfolder\Data\[bills.xlsx]2016'!$D:$D

I tried. It still does not work.

Thanks