"Replace All" to change a referenced file name

[canoeron]

I am working on a spreadsheet that will be referencing approximately 20-30 separate workbooks which all have identical sheet / column structure and have a consistent file naming convention. ("###-YYYY") Since, this is referencing separate files and not sheets with this workbook, I cannot use INDIRECT. However, I should be able to use index / match referencing provide instructions for users to copy a set of cells down and the globally change the file name. The problem is that the file directory pops up with each instance and the user has to manually select or confirm the file.

There would be approximately 15 cells with a formula similar to this example and would want to change the two instances of "403" to a new 3-digit code, say "444". Then as each year cycles over, we'd want to change all instances of 2014 to 2015.

=INDEX('C:\Users\rlagraff\Downloads\[403-2014.xlsx]Forecast Budget'!$D$15:$D$48,MATCH($P$2,'C:\Users\rlagraff\Downloads\[403-2014.xlsx]Forecast Budget'!$B$15:$B$48,0))

Any settings in Excel, or known tricks, so the user won't have to continuously click on the file and the find/replaces executes at each instance?

Thanks

[mikeTRON]

You could use a macro to pull everything to ONE file, and have the reporting off of that one file. Or load things to access and use access to make a pivot. You could use a macro to relink the formulas in each of the 15 cells.