XL 2013 Comment alimenter une seconde feuille automatiquement à partir d"une référence saisie

[samimi94]

Bonsoir à tous,

Je sollicite votre aide et votre expertise pour réaliser une synthèse rapide d'un fichier lourd en données (onglet 1) qui alimenterait un second onglet automatiquement via une référence saisie (présente dans l'onglet 1).

Je m'explique en espérant parvenir à être le plus explicite possible.

Dans mon onglet 1 je possède plusieurs données (référence, nom client, adresse, et plusieurs montants).
Il arrive qu'une même référence possède plusieurs lignes renseignées avec plusieurs montants et pas toujours les unes à la suite des autres.
Afin de na pas avoir à faire défiler toutes mes lignes (des milliers) et surtout pouvoir faire la somme de tous mes montants de manière automatique, je souhaiterai que toutes les données d'une référence soient renseignées dans un onglet 2 lorsque je saisi la référence concernée.
La référence est renseignée en colonne A de mon onglet 1, je souhaiterai savoir s'il est possible, en saisissant dans l'onglet 2 la référence de mon choix, qu'un tableau s'auto-alimente dans ce même onglet en reprenant toutes les informations de l'onglet 1 liées à cette référence.

En espérant ne pas avoir été trop brouillon, je vous joint un fichier test qui pourra probablement mieux expliquer ce que je souhaiterai obtenir comme résultat final.

Je vous remercie par avance pour votre précieuse aide.

 

[R@chid]

Re : Comment alimenter une seconde feuille automatiquement à partir d"une référence s

Bonsoir,

voir PJ


@ + +

 

[samimi94]

Re : Comment alimenter une seconde feuille automatiquement à partir d"une référence s

Bonjour à tous, R@chid,

Merci de vous êtes intéressés à mon problème.

R@chid,

Cela correspond bien à ma demande et je te remercie du travail accompli, cependant j'ai encore un petit souci.
Tout fonctionne bien avec les données présentes dans l'onglet 1 mais lorsque je rajoute des datas elles ne sont pas prises en comptes.
Exemple, si je rajoute une ligne avec la référence 108324, celle-ci n'est pas reprise dans l'onglet 2 dans mon calcul des montants.

Tu indiques que le calcul fonctionne à la condition qu'il n'y ait pas de cellules vides dans la colonne référence, or il est possible que la référence soit indiquée sur une ligne mais pas dans la suivante lorsqu'il s'agit de la même référence. Exemple en fichier joint.


Comment faire ?

Par avance merci pour votre aide.

Samimi94.

 

[job75]

Re : Comment alimenter une seconde feuille automatiquement à partir d"une référence s

Bonjour samimi94, R@chid,

R@chid a bien écrit :

Necessite que la colonne des refrences ne contienne pas de cellules vides

Si l'on ne veut pas compléter les références en Feuil1 on peut le faire dans cette petite fonction VBA :

Function Tableau(r As Range)
'complète les cellules vides en 1ère colonne
Dim t, i&
t = Intersect(r, r.Parent.UsedRange)
For i = 2 To UBound(t)
  If t(i, 1) = "" Then t(i, 1) = t(i - 1, 1)
Next
Tableau = t 'matrice
End Function

Gros avantage supplémentaire : le tableau source est rendu dynamique, pas besoin de délimiter la plage.

Fichier joint.

A+

 

[samimi94]

Re : Comment alimenter une seconde feuille automatiquement à partir d"une référence s

Re bonjour à tous,

Job75, R@chid,

C'est exactement le résultat que je souhaitais obtenir.

Merci beaucoup, cela va me faire gagner un temps de dingue, et tout cela grâce à votre expertise et votre générosité.

Bonne continuation.

Samimi94.

 

[job75]

Re : Comment alimenter une seconde feuille automatiquement à partir d"une référence s

Re,

Avec une fonction plus élaborée :

Function Tableau(ref, source As Range, dest As Range)
'complète les cellules vides en 1ère colonne
Dim ts, ncol%, td(), i&, n, j
ts = Intersect(source, source.Parent.UsedRange)
ncol = dest.Columns.Count
ReDim td(1 To dest.Rows.Count, 1 To ncol)
For i = 2 To UBound(ts)
  If ts(i, 1) = "" Then ts(i, 1) = ts(i - 1, 1)
  If ts(i, 1) = ref Then
    n = n + 1
    For j = 1 To ncol
      td(n, j) = IIf(ts(i, j + 1) = "", "", ts(i, j + 1))
    Next
  End If
Next
'---pour éviter les zéros en bas de tableau---
For i = n + 1 To UBound(td)
  For j = 1 To ncol
    td(i, j) = ""
  Next
Next
Tableau = td 'matrice
End Function

la formule matricielle de la plage de destination D3:F14 devient très très simple :

=Tableau(B3;Feuil1!A:D;D3:F14)

Fichier (2).

A+

 

[samimi94]

Re : Comment alimenter une seconde feuille automatiquement à partir d"une référence s

Job75,

Merci pour ce nouveau fichier.

Petite question, si je souhaite rajouter des lignes au delà de F14 dans mon tableau des montants (onglet 2) comment cela se passe-t-il ? L'insertion de ligne ne fonctionne pas, je suppose que cela est dû au code mais je ne sais pas comment m'y prendre.

Merci.

 

[samimi94]

Re : Comment alimenter une seconde feuille automatiquement à partir d"une référence s

Job75,

En plus de ma question précédente, peux-tu jeter un œil à mon total, c'est étrange mais le résultat n'est pas correct ?

Merci beaucoup.

 

[samimi94]

Re : Comment alimenter une seconde feuille automatiquement à partir d"une référence s

Job75,

Pour l'erreur dans le montant total, c'est bon je viens de comprendre que cela provenait du séparateur.
Lorsque je mets une virgule le résultat est faux mais dès qu'il y a un point c'est OK.

Reste donc à savoir si j'ai la possibilité de rajouter des lignes dans mon tableau montant. Si cela ne peut se faire aisément ce n'est pas grave ta première version de fichier fonctionne bien ;-)

Merci.

Samimi94

 

[job75]

Re : Comment alimenter une seconde feuille automatiquement à partir d"une référence s

Re,

Il faut bien comprendre que la formule est entrée matriciellement d'un seul coup sur toute la plage D3:F14.

Si l'on veut augmenter cette plage d'une ligne il faut d'abord supprimer la validation matricielle :

- sélectionner D3:F14

- mettre le curseur dans la barre de formule

- valider par Ctrl+Entrée (donc sans la touche Maj)

Ensuite on redéfinit la plage :

- sélectionner D3:F15

- entrer la formule (modifiée éventuellement) dans la barre de formule

-Valider par Ctrl+Maj+Entrée.

Je termine en peaufinant la fonction où le 3ème argument est rendu facultatif :

Function Tableau(ref, source As Range, Optional dest As Range)
'complète les cellules vides en 1ère colonne du tableau source
Dim ts, ncol%, td(), i&, n&, j%
If dest Is Nothing Then Set dest = Application.Caller
ts = Intersect(source, source.Parent.UsedRange)
ncol = dest.Columns.Count
ReDim td(1 To dest.Rows.Count, 1 To ncol)
For i = 2 To UBound(ts)
  If ts(i, 1) = "" Then ts(i, 1) = ts(i - 1, 1)
  If ts(i, 1) = ref Then
    n = n + 1
    For j = 1 To ncol
      td(n, j) = IIf(ts(i, j + 1) = "", "", ts(i, j + 1))
    Next
  End If
Next
'---pour éviter les zéros en bas de tableau---
For i = n + 1 To UBound(td)
  For j = 1 To ncol
    td(i, j) = ""
  Next
Next
Tableau = td 'matrice
End Function

Fichier (3).

Nota : l'intérêt du 3ème argument est d'indiquer la plage qu'il faut valider matriciellement.

Mais il est aussi simple de colorer cette plage pour le rappeler.

Edit : dans la formule en B4 une petite coquille de R@chid, mettre +4 à la place de +5.

A+

 

[samimi94]

Re : Comment alimenter une seconde feuille automatiquement à partir d"une référence s

Re Job75,

Super, ça fonctionne !

Si je peux poser une dernière petite question.
Dans mon tableau toi-même et R@chid avait réussit à simplifier ma synthèse pour récupérer les montants correspondant mais si je souhaite récupérer une autre information de l'onglet 1 qui ne correspond pas à un montant mais à une date par exemple, que dois-je mettre comme formule ?

Exemple : Dans l'onglet 1 si je rajoute une colonne date, est-il possible de récupérer les dates dans mon onglet 2 face à chaque montant ?

Encore merci pour tout.

Samimi94.

 

[BOISGONTIER]

Re : Comment alimenter une seconde feuille automatiquement à partir d"une référence s

Bonsoir,

Avec fonction perso matricielle:
Récupère les colonnes spécifiées d'une BD en fonction de 1 ou 2 critères
Les colonnes peuvent être récupérées dans un ordre quelconque

Function FiltreBD(BD As Range, colCrit1, critere1, ColResult, Optional colcrit2, Optional critere2, Optional ColTri)

-Sélectionner D2:G14
=filtrebd(Feuil1!A2:K100;1;B3;{2;3;4;11})
-Valider acec maj+ctrl+entrée

Autres exemples:
http://boisgontierjacques.free.fr/fichiers/Cellules/FiltreBD2.xls
http://boisgontierjacques.free.fr/fichiers/Cellules/FiltreBD3.xls
http://boisgontierjacques.free.fr/fichiers/fonctionsperso/FiltreBD3.xls

-Les fonctions personnalisées (UDF)peuvent être utilisées comme des fonctions standards par des personnes qui ne connaissent pas VBA
-Elles sont beaucoup plus rapides que les formules matricielles.
-Elles sont réutilisables.

Cf Fonctions personnalisées


JB

 

[samimi94]

Re : Comment alimenter une seconde feuille automatiquement à partir d"une référence s

Bonsoir Boisgontier, le forum,

Vous êtes tous vraiment géniaux.
Merci beaucoup, à vous trois vous m'offrez le résultat rêvé.

Milles mercis et bonne continuation.
Bonne soirée et bonne fin de WE.

Samimi94.

 

[job75]

Re : Comment alimenter une seconde feuille automatiquement à partir d"une référence s

Re, hello JB,

Regardez ce fichier (4), j'ai juste inséré les colonnes des dates, comme vous le souhaitez.

La fonction VBA que j'ai donnée est inchangée.

Bonne soirée.

 

[samimi94]

Re : Comment alimenter une seconde feuille automatiquement à partir d"une référence s

Job75,

Résultat parfait. Vos 2 méthodes sont parfaites.

Boisgontier,

Juste une chose, lorsque j'applique ta règle dans mon fichier d'origine j'ai les 0 ou des dates 00/01/1900 quand les cellules doivent être vides.
Mais pas grave j'ai tout ce qu'il me faut.

Encore merci à vous et bonne soirée.

Samimi94.