automatically updating row information in CORREL function

I am analyzing data sets in Excel. I am determining whether there is a correlation between the data set in File 1 and multiple data sets in File 2. File 3 contains the correlation analyses of the data set in File 1 with the other data sets in File 2. See example below. Rather than manually entering in the new row and column information for each data set in the Excel CORREL equation I would like to create a function in Excel that essential does this.

For Cell A1 of File 3 the Excel function is: = CORREL('File1'!$A$1:$Z$100,'File2'!A1:Z100),
For Cell A2 of File 3 the Excel function is: = CORREL('File1'!$A$1:$Z$100,'File2'!A101:Z200),
For Cell A3 of File 3 the Excel function is: = CORREL('File1'!$A$1:$Z$100,'File2'!A201:Z300),
and so on. Thank you.

Please Login or Register  to view this content.

I simplified the CORREL equations that I initially posted and thought I'd be able to apply the solutions what were posted. Unfortunately I haven't been able to get them to work. My actual CORREL equations are:

CORREL('Total Observed Ig Usage'!$C$2:$BC$215,'[World Development Indicators Economic Policy & Debt National Accounts (downloaded 01-02-2014).xls]Sorted Data'!C$2:$BC$215)

CORREL('Total Observed Ig Usage'!$C$2:$BC$215,'[World Development Indicators Economic Policy & Debt National Accounts (downloaded 01-02-2014).xls]Sorted Data'!C$216:$BC$429)

Thank you!

I got the dreaded "#REF!" error when I copied the formula directly into Excel.

I used the Evaluate Formula to check where the error occurs. It looks like it blows up at the last step. The step before blow up looks like ... Sorted Data'!$C$2:$BC$429,214,54)) and then ...Sorted Data'!$C$2:#REF!).