Accueil

La fonction Indirect()
Choix d'un tableau
Récupération d'informations des onglets
Indirect()/Adresse
Indirect vers classeur ouvert
Onglet précédent
Onglet précédent pour onglets non génériques
Cumul avec onglet précédent
Récapitulatif onglets
Liste des cellules B2 des feuilles d'un classeur
Consolidation de classeurs ouverts
Indirect() et champs dynamiques
Comparatif prix fournisseurs
Liste des feuilles entre 2 feuilles
Liste des feuilles commençant par
Recherche du mot Total CA dans une feuille
Récupération d'une cellule ou d'un champ d'un classeur fermé
Ecriture dynamique d'une formule pour accéder à un classeur fermé
Liens hypertexte vers feuille suivante et précédente

=Indirect(chaine)

La fonction Indirect() évalue une chaîne en adresse de champ.

Sur l'exemple, on veut obtenir le contenu de la cellule B2 de l'onglet Janvier, Février, Mars,..
(Le nom de l'onglet est en colonne B)

• Pour accéder à B2 de Janvier, la formule serait: =janvier!B2
• B5&"!b2" donne une chaîne Janvier!B2
• =INDIRECT(B5&"!b2") donne l'adresse Janvier!B2

Indirect

Si le nom de l'onglet comporte des espaces ou caractères spéciaux:

=INDIRECT("'" & B5&"'!b2")

Pour récupérer les noms d'onglets automatiquement

IndirectOngletRécap

S'il y a une apostrophe dans le nom de l'onglet (L'Hermitte par exemple):

=SI(A5<>"";INDIRECT("'"&SUBSTITUE(A5;"'";"''")&"'!B7");"")

Choix d'un tableau

La table des remises est choisie en fonction du choix Interne/Externe

=RECHERCHEV(B6;INDIRECT(B3);2;VRAI)

Indirect

Sur l'exemple, on recherche dans le tableau Garçon ou Fille suivant le genre

=INDEX((INDIRECT(B12));EQUIV(B13;sp;1);EQUIV(B14;cyl;1))

Indirect

Récupération d'informations sur un autre onglet

Dans l'onglet Annuel, on veut obtenir en B2 le total de Jacques en Janvier

=INDEX(janvier!B4:G4;EQUIV($A2;janvier!B1:G1;0))

Pour obtenir une formule recopiable

=INDEX(INDIRECT(B$1&"!B4:G4");EQUIV($A2;INDIRECT(B$1&"!B1:G1");0))

IndirectRecap

Autre exemple

=INDEX(INDIRECT("'"&$A2&"'!$B$2:$E$4");EQUIV(C$1;'123'!$A$2:$A$4;0);EQUIV($B2;'123'!$B$1:$E$1;0))

IndirectRecap

Indirect() /Adresse(ligne;colonne;;;feuille)

Sur la feuille Recap, on veut récupérer les quantités de Janvier,Février, ....
La formule doit être copiable.

En B2: =INDIRECT(B$1&"!"&ADRESSE(LIGNES($1:2);2))

IndirectAdresse
IndirectAdresse2

Autre exemple

L'opérateur choisit en A1 le mois (janvier,février,mars,... ) qui doit apparaître sur la feuille Accueil

En B3:

=INDEX(INDIRECT($A$1&"!$B$3:$B$7");EQUIV(Accueil!$A3;Janvier!$A$3:$A$7;0))
ou
ou
=INDIRECT(ADRESSE(LIGNES($1:3);COLONNES($A:B);;;$A$1))
ou
=INDIRECT($A$1&"!"&ADRESSE(LIGNES($1:3);COLONNES($A:B)))

IndirectAdresse

Adressage indirect vers un autre classeur ouvert

Le classeur ouvert se nomme Stock.xls

La formule pour adresser la cellule B2 de l'onglet Janvier de Stock.xls est: =[Stock.xls]janvier!$B$2

Avec Indirect, nous avons:

=INDIRECT("["&$B$2&"]"& B6 & "!b2")

Si le nom de l'onglet a des espaces ou des caractères spéciaux:

=INDIRECT("'["&$B$2&"]"& B6 & "'!b2")

Recherchev avec classeur ouvert variable

En B4:=RECHERCHEV(A4;INDIRECT("[Sem"&$B$1&".xls]BD!$A$2:$B$8");2;FAUX)

Si en B1, on a le nom du classeur Sem45.xls, la formule devient

=RECHERCHEV(A4;INDIRECT("["&$B$1&"]BD!$A$2:$B$8");2;FAUX)

RechercheVclasseurOuvert
Sem45

Récupérer une valeur de l'onglet précédent(onglets génériques)

Les noms d'onglets sont Semaine 01,Semaine 02,Semaine 03,...

Pour récupérer la cellule B7 de l'onglet précédent:

=INDIRECT("'Semaine " &TEXTE(DROITE(CELLULE("filename";$A$1);2)-1;"00")&"'!B7")

Pour obtenir une formule copiable:

=INDIRECT("'Semaine " &TEXTE(DROITE(CELLULE("filename";$A$1);2)-1;"00")&"'!"&ADRESSE(LIGNE()+1;COLONNE()))

OngletPrécédent

Si les noms d'onglets sont Semaine 1,Semaine 2,..., Semaine 52

=INDIRECT("'Semaine "&STXT(CELLULE("filename";$A$1);CHERCHE("]";CELLULE("filename";$A$1))+9;255)-1&"'!a7")

Avec des Mois Janvier,Février,...

=INDIRECT("'"&TEXTE(DATEVAL("01/"&STXT(CELLULE("filename";A1);TROUVE("]";
CELLULE("filename";A1))+1;99))-1;"mmmm")&"'!A2")

Avec Janvier 08, Février 08,...

=INDIRECT("'"&TEXTE(DATEVAL("01/"&STXT(CELLULE("filename";A1);TROUVE("]";
CELLULE("filename";A1))+1;99))-1;"mmmm aa")&"'!A2")

Onglet suivant:
=INDIRECT("'"&TEXTE(DATEVAL("01/"&STXT(CELLULE("filename";A1);TROUVE("]";
CELLULE("filename";A1))+1;99))+31;"mmmm aa")&"'!A2")

OngletPrécédentMois
OngletPrécédentSemaine

Avec des onglets nommés 01, 02, 03,...

OngletPrécédent

=INDIRECT(TEXTE(DROITE(CELLULE("filename";$A$1);2)-1;"00")&"!A2") récupère A2 de l'onglet précédent

Pour une formule copiable en ligne et colonne
=DECALER(INDIRECT(TEXTE(DROITE(CELLULE("filename";$A$1);2)-1;"00")&"!A2");LIGNE()-2;COLONNE()-3)

Avec des onglets nommés 1, 2, 3,...

OngletPrécédent2

=INDIRECT(STXT(CELLULE("filename";$A$1);TROUVE("]";CELLULE("filename";$A$1))+1;99)-1&"!A2")

Nom de l'onglet précédent pour des noms de feuilles génériques

Nom onglet précédent

01,02,03,...
=TEXTE(DROITE(CELLULE("nomfichier";$A$1);2)-1;"00")

1,2,3,...
=STXT(CELLULE("filename";$A$1);TROUVE("]";CELLULE("filename";$A$1))+1;99)-1

Feuil1,Feuil2,...
="Feuil" &STXT(CELLULE("nomfichier";$A$1);TROUVE("]Feuil";CELLULE("nomfichier";$A$1))+6;99)-1

S1,S2,S3,..
="S" &STXT(CELLULE("filename";$A$1);TROUVE("]S";CELLULE("filename";$A$1))+2;99)-1

Janvier,Février,Mars,...
=TEXTE(DATEVAL("01/"&STXT(CELLULE("filename";A1);TROUVE("]";CELLULE("filename";A1))+1;99))-1;"mmmm")

Semaine 01,Semaine 02,..
="'Semaine " &TEXTE(DROITE(CELLULE("nomfichier";$A$1);2)-1;"00")

Noms de feuilles non génériques

Dans un module

Function OngletPrecedent()
   OngletPrecedent = Sheets(Application.Caller.Parent.Name).Previous.Name
End Function

Dans le tableur

=OngletPrecedent()

Récupérer une valeur de l'onglet précédent avec noms d'onglets non génériques

Précédent

Créer un nom de champ
Nf =STXT(LIRE.CLASSEUR(1);TROUVE("]";LIRE.CLASSEUR(1))+1;99)&INDIRECT("iv65000")

Nom de la page précédente
=INDEX(Nf;EQUIV(STXT(CELLULE("filename";A1);TROUVE("]";CELLULE("filename";A1))+1;99);Nf;0)-1)

Cellule A3 de la page précédente
=INDIRECT("'"&INDEX(Nf;EQUIV(STXT(CELLULE("filename";A1);TROUVE("]";CELLULE("filename";A1))+1;99);Nf;0)-1)&"'!A3")

Cumul avec l'onglet précédent

=INDIRECT("'Sem" &TEXTE(DROITE(CELLULE("filename";$A$1);2)-1;"00")&"'!B3")+B2

IndirectCumul

Si les noms des feuilles sont 1,2,3,....
=INDIRECT(STXT(CELLULE("filename";A1);TROUVE("]";CELLULE("filename";A1))+1;99)-1&"!b3")+B2

Récapitulatif onglets

On veut obtenir un recap des cellules B1 et D10 des différents onglets

RécapOnglets

-Créer un nom de champ:
nf =STXT(LIRE.CLASSEUR(1);TROUVE("]";LIRE.CLASSEUR(1))+1;99)&INDIRECT("iv65000")

En A2:=SI(LIGNES($1:2)<=NBVAL(nf); INDEX(nf;LIGNES($1:2));"")
En B2:=SI(A2<>"";INDIRECT("'"&A2&"'!B1");"")
En C2:=SI(A2<>"";INDIRECT("'"&A2&"'!D10");"")

Autre exemple

On veut récupérer les valeurs de Martin des onglets aa,bb,cc,dd,ee,ff.

En B5: =INDEX(INDIRECT(A5 &"!B1:B10");EQUIV($B$2;INDIRECT(A5 &"!A1:A10");0))

ou =RECHERCHEV(B$2;INDIRECT(A5&"!$A$2:$B$10");2;FAUX)

RécapOnglets

Pour obtenir les noms des onglets automatiquement:

Nom de champ
nf =STXT(LIRE.CLASSEUR(1);TROUVE("]";LIRE.CLASSEUR(1))+1;99)&INDIRECT("iv65000")

En A5:=SI(LIGNES($1:2)<=NBVAL(nf);INDEX(nf;LIGNES($1:2));"")

Liste en cascade avec prix

ListeCascadePrix
ListeCascadePrixQte

Comparatif prix fournisseur

Récupère dans un onglet Comparatif le dernier tarif de chaque produit pour chaque fournisseur. on peut ajouter des fournisseurs.

=SI(D$6="";"";SI(ESTNA(EQUIV($A7;INDIRECT("'"&D$6&"'!$A$5:$A$16");0));"";
INDEX(INDIRECT("'"&D$6&"'!$D$5:$k$16");EQUIV($A7;INDIRECT("'"&D$6&"'!$A$5:$A$16");0);
NBVAL(DECALER(INDIRECT("'"&D$6&"'!D5:k5");EQUIV($A7;INDIRECT("'"&D$6&"'!$A$5:$A$16");0)-1;)))))

MFC:=MIN($D7:$H7)=D7

ou

=MIN(SI($D7:$H7<>0;$D7:$H7))=D7 (si numérique)

ComparatifPrix
ComparatifPrix2
ComparatifPrixMultiples

Autre exemple

Su un onglet Recap, on veut obtenir, pour Martin, une synthèse de ses congés (onglets Janvier,Février,Mars,..)

En B4:

=INDEX(INDIRECT($A4&"!D6:AQ25");EQUIV($A$2;Janvier!$A$6:$A$25;0);COLONNE()-1)

PlanningMensuelBarreBoutons

Récupérer les valeurs des cellules B2 des feuilles du classeur

Pour récupérer les noms des feuilles

-Créer un nom de champ:
NF:=STXT(LIRE.CLASSEUR(1);TROUVE("]";LIRE.CLASSEUR(1))+1;99)&INDIRECT("iv65000")
NBF: =CNUM(LIRE.CLASSEUR(4)&INDIRECT("iv65000"))

En A2:=SI(LIGNES($1:2)<=NBf;INDEX(nf;LIGNES($1:2));"")
En B2: =SI(A2<>"";INDIRECT(A2&"!b2");0)

IndirectFeuilles

Menu déroulant avec les cellules B2 des feuilles du classeur

-Créer les noms de champ:
NF:=STXT(LIRE.CLASSEUR(1);TROUVE("]";LIRE.CLASSEUR(1))+1;99)&INDIRECT("iv65000")
NbF: =CNUM(LIRE.CLASSEUR(4)&INDIRECT("iv65000"))

En A2: =SI(LIGNES($1:2)<=Nbf;INDIRECT(INDEX(nf;LIGNES($1:2))&"!B2");"")

En D2: Données/Validation/Liste=DECALER($A$3;;;NB.SI($A$3:$A$12;"><"&""))

IndirectFeuilles

-Si les noms des feuilles sont génériques Mois1,Mois2,...

IndirectFeuilles

En A2: =INDIRECT("'Mois"&LIGNE()-1&"'!b2")

-Si les noms des feuilles sont Janvier,Février,...

En A2: =INDIRECT(TEXTE(DATE(2009;LIGNE()-1;1);"mmmm")&"!b2")

Consolidation de classeurs ouverts Résultats 01,Résultats 02,....

En B1 : le nom du classeur
En A1: le nom du pays
En B2: Le nom de l'onglet Achats/Ventes

IndirectConsolidation
Résultat01
Résultat02

Si le nom de fichier est en B1seulement, la formule est copiable en B3:C5

En B3 :
=INDEX(INDIRECT("'["&$B$1&".xls]"&B$2&"'!$A$1:$I$10");
EQUIV($A3;INDIRECT("'["&$B$1&".xls]"&B$2&"'!$A$1:$A$10");0);
EQUIV($A$1;INDIRECT("'["&$B$1&".xls]"&B$2&"'!$A$1:$I$1");0))

Si le nom de fichier est en B1 et C1, pour obtenir une formule recopiable en B3:G5

=INDEX(INDIRECT("'["&B$1&".xls]"&B$2&"'!$A$1:$I$10");
EQUIV($A3;INDIRECT("'["&B$1&".xls]"&B$2&"'!$A$1:$A$10");0);
EQUIV($A$1;INDIRECT("'["&B$1&".xls]"&B$2&"'!$A$1:$I$1");0))

Si le nom de fichier est en B1 seulement, pour obtenir une formule recopiable en B3:G5

=INDEX(INDIRECT("'["&DECALER(B$1;0;-MOD(COLONNE();2))&".xls]"&B$2&"'!$A$1:$I$10");
EQUIV($A3;INDIRECT("'["&DECALER(B$1;0;-MOD(COLONNE();2))&".xls]"&B$2&"'!$A$1:$A$10");0);
EQUIV($A$1;INDIRECT("'["&DECALER(B$1;0;-MOD(COLONNE();2))&".xls]"&B$2&"'!$A$1:$I$1");0))

Total Qte par produit/mois

On veut pour chaque produit et chaque mois le total des qte vendues.

En C3:
=SOMME.SI(INDIRECT(TEXTE(C$2;"00")&"!C4:C50");SynthèseMois!$B3;INDIRECT(TEXTE(C$2;"00")&"!d4:d50"))

IndirectTotalProdMois

Plannning semaine

Permet de créer un planning de 52 semaines.

Planning semaine

En B6: =INDIRECT("'Sem " &TEXTE(DROITE(CELLULE("filename";$A$1);2)-1;"00")&"'!B6")+7

Consolidation d'onglets

Consolide les lignes No2 des onglets du classeur.

IndirectConso

Noms de champ
Nbf =CNUM(LIRE.CLASSEUR(4)&INDIRECT("iv65000"))
Nf =STXT(LIRE.CLASSEUR(1);TROUVE("]";LIRE.CLASSEUR(1))+1;99)&INDIRECT("iv65000")

En A2:=SI(LIGNES($1:1)<Nbf;INDIRECT("'"&INDEX(Nf;LIGNES($1:2))&"'!"&ADRESSE(2;COLONNES($A:A)));"")

Listes dynamiques et Indirect

Indirect() n'accepte pas les noms de champ dynamiques crées avec Decaler(). Sur cette version, on peut ajouter des items
en ligne et en colonne.

Cascade_indirect dyn.xls

1- Nommer Marque =DECALER(Listes!$A$1;;;;NBVAL(Listes!$1:$1))
2 - nommer
Renault A:A
Citroën B:B
Peugeot C:C
3 - Pour le second menu en B2
Données/Validation/Liste
=DECALER(INDIRECT($A$2);1;;NBVAL(INDIRECT(A2))-1)

Liste des feuilles entre 2 feuilles

On veut obtenir la liste des feuilles entre les feuilles début cloture et fin cloture

Créer un nom de champ

Nf =STXT(LIRE.CLASSEUR(1);TROUVE("]";LIRE.CLASSEUR(1))+1;99)&INDIRECT("iv65000")

En B4:=SI(EQUIV("début cloturé";Nf;0)+LIGNES($1:1)<EQUIV("fin cloturé";Nf;0);INDEX(Nf;EQUIV("début cloturé";Nf;0)+
LIGNES($1:1));"")

NomsFeuilles

Liste des feuilles commençant par FRA

Pour obtenir les noms des feuilles commençant par FRA:

-Créer un nom de champ NomsFeuilles:
=STXT(LIRE.CLASSEUR(1);TROUVE("]";LIRE.CLASSEUR(1))+1;99)&INDIRECT("iv65000")

Liste des feuilles commençant par FRA
-En D2: =SI(COLONNES($A:A)<=SOMME(SI(GAUCHE(NomsFeuilles;3)=$B$1;1));
INDEX(NomsFeuilles;PETITE.VALEUR(SI(GAUCHE(NomsFeuilles;3)=$B$1;
COLONNE(INDIRECT("A1:"&ADRESSE(1;NBVAL(NomsFeuilles)))));COLONNES($A:A)));"")

ListeFeuillesCommeçantPar

Recherche du mot TotalCA: dans une feuille

On recherche le total CA dans une feuille sans connaître la cellule où est situé le total.

En B3:
=SI(A3<>"";INDEX(INDIRECT("'"&A3&"'!A1:M30");
MIN(SI(INDIRECT("'"&A3&"'!A1:M30")="Total CA:";LIGNE(INDIRECT("'"&A3&"'!A1:M30"))));
MIN(SI(INDIRECT("'"&A3&"'!A1:M30")="Total CA:";COLONNE(INDIRECT("'"&A3&"'!A1:M30"))))+1);0)
Valider avec maj+ctrl+entrée

ChercheMot

Récupération d'information dans un classeur fermé variable

On veut récupérer la cellule B15 de Ca2009,CA2010,Ca2011

LectureClasseurFermé

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$B$2" Then
    Chemin = ThisWorkbook.Path
    Fichier = [B2]
    If Dir(Chemin & "\" & Fichier) <> "" Then
    [B5].Formula = "='" & Chemin & "\[" & Fichier & "]Feuil1'!B15"
  Else
    MsgBox "Fichier inconnu!"
  End If
End If
End Sub

Récupération d'un champ

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$B$2" Then
    ChampOuCopier = "A5:B17"
    Chemin = ThisWorkbook.Path
    Fichier = [B2]
    onglet = "Feuil1"
    ChampAlire = "A1:B13"
    LitChamp ChampOuCopier, Chemin, Fichier, onglet, ChampAlire
  End If
End Sub

Sub LitChamp(ChampOuCopier, Chemin, Fichier, onglet, ChampAlire)
  Range(ChampOuCopier).FormulaArray = "='" & Chemin & "\[" & Fichier & "]" & onglet & "'!" & ChampAlire
  Range(ChampOuCopier) = Range(ChampOuCopier).Value
End Sub

Ecriture dynamique d'une formule pour accéder à une information d'un classeur fermé variable

Le chemin du fichier et le fichier sont variables. La fonction Indirect() ne fonctionne pas avec un classeur fermé.
La formule RechercheV() est écrite dynamiquement à chaque fois que le chemin ou le fichier sont modifiés.
La table de recherche dans le classeur fermé est nommée Produit.

RechercheVDynamique

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$F$2" Or Target.Address = "$F$5" And Target.Count = 1 Then
    If [F2] <> "" And [F5] <> "" Then
       ChampFormule = "C2:C4"
       chemin = Range("F2")
       fichier = Range("F5")
       NomTableRecherche = "produit" ' nom du champ
       If Dir(chemin & "\" & fichier) <> "" Then
          Range(ChampFormule).Formula = _
            "=VLOOKUP(B2," & "'" & chemin & "\" & fichier & "'!" & NomTableRecherche & ",2,false)"
       Else
           MsgBox "fichier inconnu"
       End If
    End If
  End If
End Sub

Si la table de recherche dans le classeur fermé n'est pas nommée:

Range(ChampFormule).Formula = _
"=VLOOKUP(B2," & "'" & chemin & "\[" & fichier & "]Janvier'!$D$2:$E$5" & ",2,false)"

Pour transformer les formules en valeur, ajouter

Range(champFormule) = Range(champFormule).Value

Liens hypertextes vers feuille suivante et feuille précédente

Créer un nom de champ
Nf =STXT(LIRE.CLASSEUR(1);TROUVE("]";LIRE.CLASSEUR(1))+1;99)&INDIRECT("iv65000")

=SI(EQUIV(STXT(CELLULE("filename";B1);TROUVE("]";CELLULE("filename";B1))+1;99);Nf;0)>1;
LIEN_HYPERTEXTE("#"&INDEX(Nf;EQUIV(STXT(CELLULE("filename";B1);TROUVE("]";CELLULE("filename";B1))+1;99);Nf;0)-1)&"!a1";"Précédent");"")

=SI(EQUIV(STXT(CELLULE("filename";B1);TROUVE("]";CELLULE("filename";B1))+1;99);Nf;0)<NBVAL(Nf);
LIEN_HYPERTEXTE("#"&INDEX(Nf;EQUIV(STXT(CELLULE("filename";B1);TROUVE("]";CELLULE("filename";B1))+1;99);Nf;0)+1)&"!a1";"Suivant");"")

Liens hypertextes suivant précédent