Suite logique de feuille en feuille

Bonjour,

J'ai beaucoup cherché sur Google après la réponse à ma question mais je ne trouve pas. Je m'en remets donc à vous.

J'essaye de créer un suite logique où Excel devrait comprendre qu'il faut à chaque fois aller chercher les données à la feuille suivante.
Concrètement : j'ai plusieurs feuilles avec un tableau par feuille. La dernière feuille est un tableau de synthèse. La première colonne de cette feuille de synthèse (dont la première case commence en B5) donnerait ceci :
.......... B..........
5 ='Feuil1'!$B$9
6 ='Feuil2'!$B$9
7 ='Feuil3'!$B$9
.
.
.
J'aimerais utiliser la poignée de recopie pour que Excel automatise cela et comprenne que je veux à chaque fois prendre la cellule B9 de chaque page. Mais il ne le fait pas. Soit il recopie "feuil1" dans chaque case (si j'étire la poignée en sélectionnant la case B5), soit il me refait la suite d'après les cases sélectionnées (si j'étire la poignée en sélectionnant les cases B5 à B7).
Avez-vous une formule magique?

Merci beaucoup d'avance pour le temps que prendrez pour m'aider !

BforD9615