Help Referencing Other Spreadsheets

[abbab]

I receive monthly files detailing metrics we need to report on quarterly. In each file there is a separate tab for each site with the metrics for that site as well as a summary sheet. Information on the summary sheet is calculated using the following formula: =COUNTIF('Site - Site 1'!I5:I300,'Summary - By Site'!H3). The cells the data is in never changes.

We have a quarterly file that we use to compile the information each month. The quarterly file is set up similarly to the monthly file with tabs for each site and then a summary tab. I have been using the following array to pull the information from the monthly files to the quarterly file.

=IF(ISNA(VLOOKUP($A3, ('[FILE LOCATION AND NAME]Summary - By Site'!$H$3:$I$25),2,FALSE)),0,VLOOKUP($A3, ('[FILE LOCATION AND NAME]Summary - By Site'!$H$3:$I$25),2,FALSE))

This works, but is cumbersome. I need to do a find/replace to change the file name for each month and then do a find/replace to update where to pull the location from each site (i.e. site 1 will also be in $A$2:$B$11, site 2 will always be in $C$2:$D$11 so on and so forth…). To make matters worse, every time I run the array a dialog box appears asking me to navigate to the file where the information is stored.

I am looking for a way to pull the information for each month and update the location of the data for each site that is easier and less cumbersome than this and avoids having the dialog box appear every time a file location is mentioned. (If is that even possible?) The naming conventions for the monthly files never change. Data will always need to be pulled from ‘Monthly Report – January’, ‘Monthly Report – February’, etc. and the same cells will always be referenced each month. It is just the month that changes and the cell reference for each site. I have attached a sample file in case anyone would like to reference it.


I am new to VBA so simplicity is best. Please let me know if you have any questions.
Thank you in advance,
Abby

[Teeroy]

You don't need VBA for this, use the INDIRECT worksheet function and reference the file location and name to a cell. eg. "=INDIRECT("'["& C1 & "]" & "Sheet1'!$D$5")" will show the value of a cell in a worksheet of another workbook where the name of that workbook is stored in cell C1. The only (minor) drawback is the other workbook needs to be open but you could open that workbook via VBA referencing the same cell/s to get the details. If filepath were in A1 and filename in A2

[VBA]Sub test()
Workbooks.Open (Range("A1").value & "\" & Range("A2").value)
End Sub[/VBA]