Retourner valeur en fonction de l'onglet

Le 26/01/2019 à 09:16

Bonjour à tous

Je cherche actuellement à reporter des informations présentes dans différents onglets dans un onglet sommaire. J'ai déjà créer cette onglet grâce à un macro qui met à jour le sommaire quand on clique sur un bouton. Le nom des onglets se trouve en colonne A et la valeur à chercher en colonne B. Dans chaque onglet, l'information se trouve dans la colonne numéro 5 (soit la E) mais se trouve sur une ligne différente.

  • Sachant que je souhaite faire référence à la case où le nom de l'onglet est présent pour chercher l'information.
  • Sachant que certains onglets comportent une apostrophe dans leur nom.
  • Sachant que pour un onglet donné, pour la colonne numéro 1 (soit la A) si le terme "Water" apparait, je veux retourner une information croisée entre la colonne 5 "Indice" et la ligne comportant le terme "Contenu de X (avec)" dans la colonne B de l'onglet sommaire. Sinon (pas de terme "Water" présent), je veux retourner l'information entre la colonne 5 et la ligne avec le terme "Contenu de X" dans la colonne B de l'onglet sommaire.
  • Sachant que j'ai une centaine d'onglets, et que parfois le terme à rechercher dans la première colonne possède un espace à la fin, par exemple "Contenu de X " au lieu de "Contenu de X".

Voici une ébauche, qui ne fonctionne actuellement pas (du tout) même pour les onglets sans apostrophe:

=SI(INDIRECT("'"&A1&"'!A:A")="Water";RECHERCHEV("contenu de X (avec)";INDIRECT("'"&A1&"'!A:A");5;FAUX);RECHERCHEV("Contenu de X";INDIRECT("'"&A1&"'!A:A);5;FAUX))

J'espère ne pas avoir été trop longue,

Merci

Le 26/01/2019 à 09:37

Bonjour,

auriez vous un fichier pour illustrer votre demande ?

Sinon le fait de mettre si (A:A = "Water".... ne peut fonctionner car comment une colonne entière avec plusieurs valeurs peut être = à quelque chose...

Dans votre cas il faut passer par une formule matricielle qui accepte des contrôle de plage par rapport à une valeur.

Soit la formule est matricielle et elle est validée par les triples touches [Shift]+[Ctrl]+[Entrée] soit elle est matricielle par "nature" comme par exemple SOMMEPROD.

Alors je pense qu'avec un fichier support, une réponse arrivera vite, voir même sans pour les plus chevronnés de nous !

@ bientôt

LouReed

Le 26/01/2019 à 10:19

Bonjour et merci pour votre réponse rapide!

Je ne peux malheureusement pas mettre le fichier original car lié à mon travail mais voici un exemple qui illustre bien la situation.

Ne vous formalisez pas sur la forme ni le contenu surtout quand il est vide

Bonne journée!

37exemple-tableur.xlsx (12.10 Ko)
37exemple-tableur.xlsx (12.10 Ko)
Le 27/01/2019 à 15:40

Personne?

Le 27/01/2019 à 15:52

Bonjour,

j'y travaille un peu ..

Pour pouvoir faire référence à un onglet comportant une apostrophe par INDIRECT il faut substituer l'apostrophe par une double apostrophe. La formule suivante sur votre feuille "Feuil1" en colonne D ligne 2 vous donne 1 si Water est compris dans la colonne de l'onglet dont le nom est en colonne A et 0 si il n'y est pas, sans SUBSTITUE, une erreur s'affiche, avec les résultat affiché est le bon :

=NB.SI(INDIRECT("'"&SUBSTITUE(A2;"'";"''")&"'"&"!A:A");"Water")

C'est un début, on sait s'il y a Water ou pas, merci de bien vouloir r expliquer les différents résultat attendus.

@ bientôt

LouReeD

Le 27/01/2019 à 16:55

Salut Ruizzus,

Salut LouReed,

quelque chose ainsi ?

Un double-clic en feuille 'Extract' démarre la macro.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'
Dim iRow%, iTRow%, sData$
'
Cancel = True
'
Application.ScreenUpdating = False
'
iRow = 1
'
Cells.Delete
[B1] = "Indice (sans)"
[C1] = "Indice (avec)"
[D1] = "Indice X"
Range("B1:D1").Interior.Color = RGB(195, 195, 195)
'
On Error Resume Next
For x = 1 To Sheets.Count
    If Sheets(x).Name <> "Extract" Then
        With Sheets(x)
            iRow = iRow + 1
            sData = IIf(InStr(UCase(.Range("A2").Value), "WATER") > 0, "Contenu de X (avec)", "Contenu de X")
            iTRow = .Range("A:A").Find(what:=sData, lookat:=xlPart).Row
            If Not rCel Is Nothing Then
                Cells(iRow, 1) = Sheets(x).Name
                Cells(iRow, IIf(sData = "Contenu de X (avec)", 3, 4)) = .Cells(iTRow, 5)
                If sData = "Contenu de X (avec)" Then Cells(iRow, 2) = .Cells(iTRow + 1, 5)
            End If
        End With
    End If
Next
On Error GoTo 0
Columns("A:D").AutoFit
Range("A1").CurrentRegion.Borders.LineStyle = xlContinuous
'
Application.ScreenUpdating = True
'
End Sub

A+

8indiceavecsans.xlsm (21.27 Ko)
Le 27/01/2019 à 17:04

Bonsoir,

voir fichier joint :

Mais de mon coté je ne comprend pas l'erreur #Ref!...

Je la comprend du fait que le résultat renvoie la ligne 0 qui n'existe pas, mais c'est le 0 que je ne comprend pas...

Mais cela va peut-être faire avancer votre recherche.

@ bientôt

LouReeD

Nota : petit bug du site je ne peux poster le fichier...

Voici les deux formules, l'une en ligne 2 colonne B de la Feuil1 :

=SI(NB.SI(INDIRECT("'"&SUBSTITUE(A2;"'";"''")&"'"&"!A:A");"Water")>0;INDIRECT("'"&SUBSTITUE(A2;"'";"''")&"'"&"!E"&SOMMEPROD((SUPPRESPACE(INDIRECT("'"&SUBSTITUE(A2;"'";"''")&"'"&"!A1:A100"))="Contenu de X (avec)")*(LIGNE(INDIRECT("'"&SUBSTITUE(A2;"'";"''")&"'"&"!A1:A100")))));"")

A tirer vers le bas, vous remarquerez que je l'ai limitée aux 100 premières lignes des feuilles...

et l'autre en colonne C :

=SI(NB.SI(INDIRECT("'"&SUBSTITUE(A2;"'";"''")&"'"&"!A:A");"Water")=0;INDIRECT("'"&SUBSTITUE(A2;"'";"''")&"'"&"!E"&SOMMEPROD((SUPPRESPACE(INDIRECT("'"&SUBSTITUE(A2;"'";"''")&"'"&"!A1:A100"))="Contenu de X")*(LIGNE(INDIRECT("'"&SUBSTITUE(A2;"'";"''")&"'"&"!A1:A100")))));"")

Vous remarquerez que j'ai intégré le test du mot "Water" dans la formule.

Vous remarquerez que je n'ai peut être pas tout compris entre "Water" et "Avec" et "Sans", mais l'idée de la formule reste la même, @ vus de jouer maintenant !

@ bientôt

LouReeD

Le 27/01/2019 à 17:06

Alors curulis57, c'est pour qui le petit cœur ?

@ bientôt

LouReeD

Le 27/01/2019 à 17:26

Pour toi, LouReed, sans contestation, bien sûr!

Le 27/01/2019 à 22:53

Merci curulis57 et merci LouReed!

Je ne peux pas essayer les macros aujourd'hui avec mon pc, mais je vais essayer cette semaine!

Voici le code final que j'ai appliqué avec votre aide:

  • D'après le nom de l'onglet présent dans la Feuil1 colonne A, qu'il comporte ou non une apostrophe.
  • Si Water est présent dans l'onglet alors je cherche la valeur présente pour la colonne E et la ligne "Contenu de X (avec)", la valeur est renvoyée dans la colonne B de la "Feuil1", si Water est absent aucune valeur n'est renvoyée.
  • Si Water est présent dans l'onglet alors je cherche la valeur présente pour la colonne E et la ligne "Contenu de X (sans)", la valeur est renvoyée dans la colonne C de la "Feuil1", si Water est absent alors je cherche la valeur présente pour la colonne E et la ligne "Contenu de X", la valeur est renvoyée dans la colonne C de la "Feuil1".

Voici le code final que j'ai appliqué grâce à votre aide :

-Colonne B Feuil1

=SI(NB.SI(INDIRECT("'"&SUBSTITUE(A2;"'";"''")&"'"&"!A:A");"Water")=0;"";INDIRECT("'"&SUBSTITUE(A2;"'";"''")&"'"&"!E"&SOMMEPROD((SUPPRESPACE(INDIRECT("'"&SUBSTITUE(A2;"'";"''")&"'"&"!A1:A100"))="Contenu de X (avec)")*(LIGNE(INDIRECT("'"&SUBSTITUE(A2;"'";"''")&"'"&"!A1:A100"))))))

Colonne C Feuil1

=SI(NB.SI(INDIRECT("'"&SUBSTITUE(A2;"'";"''")&"'"&"!A:A");"Water")=0;INDIRECT("'"&SUBSTITUE(A2;"'";"''")&"'"&"!E"&SOMMEPROD((SUPPRESPACE(INDIRECT("'"&SUBSTITUE(A2;"'";"''")&"'"&"!A1:A100"))="Contenu de X")*(LIGNE(INDIRECT("'"&SUBSTITUE(A2;"'";"''")&"'"&"!A1:A100")))));INDIRECT("'"&SUBSTITUE(A2;"'";"''")&"'"&"!E"&SOMMEPROD((SUPPRESPACE(INDIRECT("'"&SUBSTITUE(A2;"'";"''")&"'"&"!A1:A100"))="Contenu de X (sans)")*(LIGNE(INDIRECT("'"&SUBSTITUE(A2;"'";"''")&"'"&"!A1:A100"))))))

J'ai effacé les cases avec "Contenu de X / (sans) / (avec) " (qui comportent un espace à la fin) avec Rechercher/Remplacer.

Merci encore!

13exemple-tableur-done.xlsx (12.78 Ko)
Rechercher des sujets similaires à "retourner valeur fonction onglet"