slope of a line (x coefficient of trend line ) in chart & coordinate

[fakhteh]

Hi every one

I want to make sheet like "result" in attached file with columns : xutm , yutm ,and slope of trend line.i found trend line with scatter chart before in every sheets so just need to choose coefficient of x in equation for every coordinate
so please help me with macro code because i have many sheets

thank you so much

[Bo_Ry]

Please try at C2

=SLOPE(INDIRECT("'"&A2&"'!R2:R19"),INDIRECT("'"&A2&"'!Q2:Q19"))

[fakhteh]

hi
i ran your formula and faced with error #ref! i do not know why
what is the role of column A in your formula
thank you

[Bo_Ry]

A2 is sheet name of the slope to be pulled.

[fakhteh]

Hi again
it works thank you well done
could you please send me the code or formula to have xutm that equals to sheet name= A2 and then B2 as yutm of the sheet ;so that i have 3 columns
xutm=A2=Sheet name
yutm=B2
slope=c2
so then i will have result sheet with all coordinate point(x utm-y utm ) with their data line slope
i hope if i could explain well, forgive me for my weak English language
thank you very much

[Bo_Ry]

Press Ctrl+F3
New Name : SheetName
=MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1))+1,9)


A2
=IF(ROWS(A$2:A2)< COUNTA(SheetName),INDEX(SheetName,ROWS(A$2:A2)),"")

B2
=IF(A2="","",INDIRECT("'"&A2&"'!h2")&"")

C2
=IF(B2="","",SLOPE(INDIRECT("'"&A2&"'!R2:R19"),INDIRECT("'"&A2&"'!Q2:Q19")))


Save as xlsb

[fakhteh]

hi
first of all i appreciate your help
is it possible to change a bit more
in part R2:R19 and Q2:Q19
because it starts from R2 and Q2 but i do not know where it ends maybe i have to put $ (R2:R$ and Q2:Q$)
I hope i could explain well so forgive me for my weak language

=IF(B2=""؛""؛SLOPE(INDIRECT("'"&A2&"'!R2:R19")؛INDIRECT("'"&A2&"'!Q2:Q19")))

thank you so much

[JeteMc]

Please try the following:
On the 449447 and 452085 sheets paste the following into cell U1: =COUNT(Q:Q)+1
On the Result sheet paste the following into cell H2 and then copy down: =INDIRECT("'"&A2&"'!u1")
On the Result sheet paste the following modification of Bo_Ry's formula into cell B2 and then copy down: =IF(B2="","",SLOPE(INDIRECT("'"&A2&"'!R2:R"&H2),INDIRECT("'"&A2&"'!Q2:Q"&H2)))
Let us know if you have any questions.