Help Converting Sum If Array Formula to use INDIRECT

[sjrider]

Hello - I need your help. I currently have an array Sum If formula that sums data from a column in another Workbook. Each month the worksheet that the data is in changes to a new "Mmm Yy" tab in the workbook (from "Jan 16" to "Feb 16"). Because of this I would like to add the INDIRECT formula to the current formula so I can just put "Jan 16" into a cell and change it once.

=SUM(IF('\\hofile1p\Files\NFS\2016\General Accounting\House Closing Trackers\[House Closing Tracker Pacific Northwest.xlsx]Jan 16'!$E$5:$E$1000>=$T$2,1,0))



I tried this formula, but I am receiving a #REF! error.

=SUM(IF(INDIRECT("'\\hofile1p\Files\NFS\2016\General Accounting\House Closing Trackers\[House Closing Tracker Pacific Northwest.xlsx]"&Y1&"'!$E$5:$E$1000")>=$T$2,1,0))

Does anyone have experience with this type of situation?

 

[BobUmlas]

INDIRECT must reference an open workbook. Otherwise, as you see, you get a #REF! error.

 

[sjrider]

Thank you! Is there any way to do what I'm trying to achieve outside of an INDIRECT formula?