try this, in sheet1 C15, copied down
=OFFSET(INDIRECT("'"&LEFT($D$12,5)&"'!$A$1"),3,ROW(A1))

then in D15, copied down and across
=INDEX(INDIRECT("'"&LEFT($D$12,5)&"'!$A$1:$CD$46"),MATCH("mg/kg "&D$14,'SP024'!$A$1:$A$46,0),ROW(A2))

let me know how you make out