Référence dynamique à une feuille de calcul ou à un classeur Excel

Supposons que vous disposiez de données au même format réparties sur plusieurs feuilles de calcul ou plusieurs classeurs et que vous souhaitiez récupérer ces données de manière dynamique dans une autre feuille. La fonction INDIRECT peut vous permettre de réaliser cela rapidement.

Faire référence à des cellules dans une autre feuille de manière dynamique
Faire référence à des cellules dans un autre classeur de manière dynamique

Faire référence à des cellules dans une autre feuille de manière dynamique

Imaginons quatre feuilles de calcul, chacune recensant les ventes trimestrielles d’un commercial différent. Vous souhaitez créer une feuille de synthèse capable d’extraire dynamiquement les ventes trimestrielles en fonction du commercial sélectionné. La formule ci-dessous vous permettra d’y parvenir facilement.

Formule générique

=INDIRECT(")'«&sheet_name&»'!Cell to return data from")

1. Comme illustré dans la capture d’écran ci-dessous, commencez par créer la feuille de synthèse en saisissant les noms des feuilles dans des cellules distinctes, puis sélectionnez une cellule vide, copiez la formule suivante et appuyez sur la touche Entrée.

=INDIRECT(«'»&B3&«'!C3»)

Remarques: Dans la formule :

• B3est la cellule contenant le nom de la feuille à partir de laquelle vous allez extraire des données ;
• C3est l’adresse de la cellule dans la feuille spécifique dont vous allez extraire les données ;
• Pour éviter de renvoyer une valeur d’erreur si B5 (la cellule du nom de feuille) ou C3 (la cellule dont vous allez extraire les données) est vide, veuillez encadrer la formule INDIRECT avec une fonction SI, comme indiqué ci-dessous :
  =IF(OR(B3="",C3=""),"",INDIRECT($B$3&"!C3"))
• Si vos noms de feuilles ne contiennent aucun espace, vous pouvez utiliser directement cette formule
  =INDIRECT(B3&«!C3»)

2. Ensuite, faites glisser la poignée de recopie vers le bas pour appliquer la formule aux autres cellules. Vous avez désormais récupéré toutes les ventes du premier trimestre depuis les feuilles spécifiques.

3. Poursuivez ensuite l’extraction des ventes des autres trimestres selon vos besoins. N’oubliez pas de modifier la référence de cellule dans la formule.

Faire référence à des cellules dans un autre classeur de manière dynamique

Cette section explique comment faire référence dynamiquement à des cellules situées dans un autre classeur Excel.

Formule générique

=INDIRECT(")'[« & Book name & »]« & Sheet name & »'!" & Cell address)

Comme illustré dans la capture d’écran ci-dessous, les données que vous souhaitez récupérer se trouvent dans la colonne E de la feuille de calcul « Total des ventes » d’un classeur distinct nommé « SalesFile ». Procédez comme suit, étape par étape, pour effectuer cette opération.

1. Commencez par renseigner les informations du classeur (nom du classeur, nom de la feuille de calcul et cellules de référence) à partir desquelles vous souhaitez extraire les données vers le classeur actuel.

2. Sélectionnez une cellule vide, copiez la formule suivante, puis appuyez sur la touche Entrée.

=INDIRECT(«'[»&$B$3&«]»&$C$3&«'!»&D3)

Remarques:

• B3contient le Nom du classeur à partir duquel vous souhaitez extraire les données ;
• C3est le nom de la feuille ;
• D3est la cellule à partir de laquelle vous allez extraire des données ;
• La valeur d’erreur #REF!s’affichera si le classeur référencé est fermé ;
• Pour éviter la valeur d’erreur #REF!, veuillez encadrer la formule INDIRECT avec la fonction SIERREUR comme suit :
  =IFERROR(INDIRECT(«'[»&$B$3&«]»&$C$3&«'!»&D3),«»)

3. Faites ensuite glisser la poignée de recopie vers le bas pour appliquer la formule aux autres cellules.

Astuce :Si vous ne souhaitez pas que le Valeur de retour devienne une erreur après la fermeture du classeur référencé, vous pouvez directement indiquer le Nom du classeur, le Nom de la feuille de calcul et l’adresse de la cellule dans la formule, comme suit :
=INDIRECT(‹[SalesFile.xlxs]Total sales›!E3,«»)

Fonction associée