Prenons comme exemple un classeur qui contient la feuille des temps pour 5 salariés (une feuille par salarié). Une première chose très importante à noter c'est que toutes les feuilles de calcul ont la même structure.

• Les informations du lundi sont en ligne 4, le mardi en ligne 5, ....
• Tous les jours sont en colonne A
• Les heures travaillées sont toujours en colonne B

Maintenant, la question c'est de savoir comment nous allons pouvoir remplir la feuille de synthèse à partir des 5 autres feuilles. Comment lier la feuiller de synthèse à plusieurs feuilles de calcul ?

Étape 1 : Écrire la première liaison

Nous allons écrire la première liaison entre la feuille synthèse et la feuille du salarié Pierre pour la journée du Lundi

1. Dans la cellule B3, saisissez le signe =
2. Puis sélectionner la cellule qui contient le temps de Pierre (cellule B4)

=Pierre!B4

Étape 2 : Analysons la formule

La formule se décompose en 4 catégories

1. Le nom de la feuille (Pierre)
2. Le séparateur entre le nom de la feuille et la référence de la cellule (le point d'exclamation)
3. La référence de la colonne (B)
4. La référence de la ligne (4)

Étape 3 : Écrire les liaisons du lundi pour les autres salariés

Ensuite, nous allons refaire la même opération pour les autres salariés

Et en utilisant la technique pour afficher les formules dans les cellules, nous avons le résultat suivant

REMARQUE IMPORTANTE. Entre toutes ces formules, la seule chose qui change, c'est le nom de la feuille de calcul. Tout le reste est identique (!B4). Or le nom des employés est présent dans les cellules B2, C2, D2, E2 et F2. Donc l'idée c'est

• Construire une référence vers les autres feuilles de calcul en utilisant les cellules B2, C2, ...

Étape 4 : Utiliser la fonction INDIRECT

La fonction INDIRECT permet d'interpréter une référence écrite sous forme de chaînes de caractères. Donc déjà, nous allons simplement écrire une des références en tant qu'argument de la fonction INDIRECT.

=INDIRECT("Pierre!B4")

Étape 5 : Construire la référence avec une cellule

C'EST LA QUE TOUT SE JOUE ! Nous allons transformer la formule précédente pour utiliser les valeurs de la ligne 2 en tant que référence.

Donc, nous allons

1. Supprimer le nom de feuille de calcul de la référence
2. Puis, se positionner EN DEHORS de la chaîne de caractères
3. Sélectionner la cellule B2
4. Lier les 2 parties avec le symbole &

=INDIRECT(B2&"!B4")

C'EST PARFAIT ! La fonction INDIRECT retourne bien le résultat de la cellule B2 de la feuille de calcul Pierre. Si l'écriture de la référence n'est pas parfaite, la fonction renverra une erreur.

Ensuite, nous allons recopier la formule. Quand vous recopiez la formule pour le lundi uniquement, nous voyons la référence de la colonne se décaler (c'est parfait). Mais ensuite, quand nous allons faire la recopie pour les autres jours de la semaine, il faut toujours rester sur la ligne 2. C'est pour ça que nous avons mis un dollar juste avant la référence de la ligne.

Étape 6 : Et pour les autres jours ?

A ce stade, seul le nom des feuilles de calcul est utilisé comme variable. Mais nous devons aussi transformer le numéro de ligne à retourner pour ne pas toujours rester sur la cellule B4.

Ce qu'il faut faire, c'est transformer le 4 par une formule qui va renvoyer 5, 6, 7, 8 pour les autres. Pour cela, nous allons utiliser la fonction LIGNE()

=INDIRECT(B$2&"!B"&LIGNE()+1)

L'écriture LIGNE()+1 s'explique par la différence de ligne entre la feuille "Recap" et les feuilles des salariés

Le fichier de cet exemple se trouve ici.

Simplifier le travail avec Power Query

Une autre façon de procéder serait de regrouper toutes les feuilles de calcul dans une seule feuille. Ce travail peut se faire facilement avec Power Query comme c'est expliqué dans la vidéo. Ainsi, le travail d'analyse pourra se faire plus simple à construire avec un tableau croisé dynamique par exemple.