Ciao,

una formula come questa

codice:
=SOMMA(Foglio3!J7:Foglio3!J10)
può essere resa dinamica nel modo che intendi tu trasformandola così

codice:
=SOMMA(INDIRETTO(A1&"!J7"):INDIRETTO(A1&"!J10"))
e inserendo la stringa Foglio3 in A1.

Purtroppo non ho tempo di essere più dettagliato, ti ho riportato solo la prova che ho fatto al volo, ma dovresti essere in grado di applicarla facilmente al tuo caso specifico.

P.S.: quel doppio test iniziale su J40="" è inutile, ne basta uno solo.

Ciao