TCD sur plusieurs feuilles

Bonjour,

Cet article fait suite à la lecture du site de Jean-Marie Lambert où il présente une facon de changer la source des données d'un tableau croisé dynamique (TCD) en utilisant la fonction INDIRECT dans un nom dynamique pour renseigner la feuille contenant les données.

Ceci bien sur à condition que les données aient la même structure.

Le but est de réaliser ceci :

Vous pouvez retrouver mon fichier exemple ici

Je ne vais pas réécrire l'article, qui est très bien détaillé et pédagogique, mais l'idée principale est celle-ci .

On ajoute une cellule (ici A2 de la feuille TCD) contenant le nom de la feuille de la base de données concernée. Cette cellule contient une validation (Données - Validation) afin d'éviter les fautes de frappe.

La fonction Indirect permet de renvoyer les références suivantes :
- Liste1!$A$1 pour INDIRECT(TCD!$A$2&"!$A$1")
- Liste1!$A:$A pour INDIRECT(TCD!$A$2&"!$A:$A")
- Liste1!$1:$1 pour INDIRECT(TCD!$A$2&"!$1:$1")

La définition d'un nom dynamique (Insertion - Nom - Définir) pour un TCD prend la forme générale suivante :
=DECALER(Liste!$A$1;;;NBVAL(Liste1!$A:$A);NBVAL(Liste1!$1:$1))
En considérant que:
- la feuille "Liste1" contienne la base de données,
- la première ligne ne contenant que les en-têtes et
- la première colonne ne devant pas avoir de cellules vides.

D'où la combinaison finale suivante pour le nom dynamique :
=DECALER(INDIRECT(TCD!$A$2&"!$A$1");;;
NBVAL(INDIRECT(TCD!$A$2&"!$A:$A"));NBVAL(INDIRECT(TCD!$A$2&"!$1:$1")))

On crée ensuite le TCD en se basant sur ce nom dynamique comme source des données.

Finalement, en ajout à l'article de Jean-Marie Lambert, on peut créer une procédure événementielle captant le changement de valeur de la cellule A2 et lancant automatiquement l'actualisation du tableau :

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$2" Then
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotCache.Refresh
End If

End Sub

Bon amusement

Gaetan Mourmant
www.polykromy.com