How can I efficiently copy/edit COUNTIFS formulas that must reference new files daily

I have created a countifs formula that is looking up data from another work book. This is my first time using this particular function.

I am creating a Yearly work book that will need to have this formula in 365 separate rows, however, everyday new updated data gets dropped in a specific location and is named for that day so I need to have unique references in each formula. (I.E. tomorrow the data needed will be in workbook named 9-26-2015.xlsx). I am hoping there is an efficient way to do this.

I tried pulling the formula down and that did not work. Is there a less tedious way to do this other than doing it one by one? Also, not to complicate it anymore, but I need the cell in criteria 1 (BJ280) to go to (BJ281) when I copy down the data , while the cell in criteria # 2 (BW2) needs to remain as it is.

Formula

starting
=COUNTIFS('[9-25-2015.xlsx]9-25-2015'!$D:$D,BJ280,'[9-25-2015.xlsx]9-25-2015'!$V:$V,BW2)

need to reference a daily file drop so next formula should look like this and so on through9/2016:

=COUNTIFS('[9-26-2015.xlsx]9-26-2015'!$D:$D,BJ281,'[9-26-2015.xlsx]9-26-2015'!$V:$V,BW2)


Appreciate whatever feedback can be offered.

Also, if you use INDIRECT then the workbook(s) holding the source data have to be open for the formula to calculate.

Without using INDIRECT you can make a formula work with a bit of trickery.

First enter this formula, then copy it down for as many rows as you need.

Formula:

Please Login or Register  to view this content.

This will generate all of the required formula as text strings.

Next copy the entire formula range and pastespecial - values to change them to real formula.

Hi, welcome to the forum

INDIRECT will not work on closed workbooks (as jason said), but you could take a look at installing the MoreFunc add-in that lets you access closed workbooks. It can be found here...
http://www.ashishmathur.com/tag/morefunc/