Si vous avez le temps de m'expliquer comment fonctionne la formule utilisé je suis preneur !
Hé bien, prenez ce qui suit
Tout d'abord ,une autre formule qui selon moi est plus simple puisque j'utilise la colonne N qui contient le nom des feuilles. Je donnerai les explications pour ce fichier. Mais le principe reste le même pour le premier fichier fourni.
La formule est basé sur la fonction
Indirect(xxx)
Cette fonction prend le texte en paramètre et le transforme en référence.
Exemple :
Supposons que F1 = 12,34
=Indirect("F1") renvoie une référence à la cellule F1 et donc renvoie la valeur de F1 c'est à dire 12,34
C'est exactement comme si on avait écrit directement la formule
=F1
Jusqu'ici aucun intérêt pour la fonction
Indirect. Supposons maintenant qu'on veuille renvoyer une cellule de la plage AA1 à AA100 de manière
aléatoire.
On pourrait vouloir écrire la formule
=AA & ALEA.ENTRE.BORNES(1;100)
Essayez, ça ne donnera rien.
On change donc pour la formule
="AA" & ALEA.ENTRE.BORNES(1;100)
Essayez encore une fois, ça ne donnera rien si ce n'est le
texte AAxx ou xx est un nombre entre 1 et 100
Avec la fonction
indirect(), c'est une autre histoire.
=Indirect(
"AA" & ALEA.ENTRE.BORNES(1;100))
Ce qu'il y dans la fonction
indirect va renvoyer le texte
AAxx (supposons que xx = 23)
La fonction Indirect("AAxx") va renvoyer une référence à la cellule AA23.
Donc
Indirect("AA23") va renvoyer la valeur de la cellule AA23.
On a donc entré une expression texte calculée dans la fonction
Indirect et cette expression texte a été transformée en référence.
Le gros avantage, c'est que cette expression texte est une expression qu'on peut paramétrer.
Dans votre cas, vous avez en O4 la formule :
=$F$4 * 'spl 1'!$H$30
Quand on tire cette formule vers le bas, il y a des choses invariantes ($F$4 et $H$30) et une chose variable qui est le nom de la feuille spl 1, spl 2, spl 3
Or justement sp1, sp2, sp3, sont les éléments de la colonne N.
Il faut donc construite un texte qui sera paramétré par la colonne N.
Ce texte sera injecté dans la fonction
Indirect() pour renvoyer la bonne référence.
sp1 est envoyé par la cellule N4 ce qui suggère :
= N4 & "'!$H$30"
Deux remarques :
- par rapport à votre formule initiale en O4, on voit qu'on a maintenant un texte (d'où les guillemets)
- quand on tirera cette expression vers le bas, N4 prendra successivement les valeurs spl 1 puis spl 2 puis spl3, etc.
ce qui donnera :
"'spl 1'!$H$30
" puis "'spl 2'!$H$30
" puis "'spl 3'!$H$30
", etc.
Injectées dans indirect, ces expressions pointeront donc bien vers les bonnes cellules
La formule complète en O4 à tirer vers le bas sera donc :
=$F$4 * INDIRECT("'" & N4 & "'!$H$30")
Le SiErreur(...) a été ajouté ensuite pour afficher une cellule vide si la feuille
spl nn n'existe pas.
Pour la formule du premier fichier, je ne me suis pas servi de la colonne N (ce qui est bien dommage ).
Donc à la place de "'" & N4, on a mis une expression (plus compliquée) qui donne la même chose : "'spl " & LIGNES($1:1)
On remarquera que LIGNES($1:1) renvoie le nombre de lignes depuis la ligne 1.
Quand on tire vers le bas la formule LIGNES($1:1), elle devient devient LIGNES($1:2) puis LIGNES($1:3), ...
donc renvoie successivement 1 puis 2 puis 3, ...
On a donc ainsi reconstruit la suite spl 1, spl 2, spl 3, etc.
Voilà, voilou. N'hésitez pas à demander des éclaircissements complémentaires
.