Using SLOPE function across multiple sheets

[Teckstein]

I am using this formula
=SLOPE('2012:2017'!$A$2,'2012:2017'!B61)
to calculate the slope of a line. The sheets, 2012, 2013....2017 contains the data entry. The cell A2 in each sheet contains the x coordinates that are common and then B61 contains the first set of y coordinates that I want to calculate a slope for. There are additional y coordinates in other cells -- so I repeat the formula like this =SLOPE('2012:2017'!$A$2,'2012:2017'!B62 (through CV113) The problem I have is that the error #REF ! if returned for all formulas. I have tested that the '2012:2017'!$A$2 and '2012:2017'!B61 are correctly formatted by running sums and averages across these ranges.

Any ideas??

 

[Domenic]

Try...

=SLOPE(N(INDIRECT("'"&{2012,2013,2014,2015,2016,2017}&"'!A2")),N(INDIRECT("'"&{2012,2013,2014,2015,2016,2017}&"'!"&CELL("address",B61))))

or

=SLOPE(N(INDIRECT("'"&(2012+ROW(INDIRECT("1:6"))-1)&"'!A2")),N(INDIRECT("'"&(2012+ROW(INDIRECT("1:6"))-1)&"'!"&CELL("address",B61))))

Note that the formulas can be copied down and across.

Hope this helps!