RECHERCHEV dans plusieurs feuilles autre classeur

[martinette]

Bonjour à tous

Comment puis je effectuer une recherchev dans plusieurs feuilles d'un classeur ?

J'ai un formulaire de devis sur un classeur DEVIS sur lequel je tape le numéro d'article

J'effectue une recherchev sur un autre classeur, mais mes articles sont classés dans différents onglets (baignoires, lavabos, wc... - jusqu'à 10 onglets)

Actuellement, ma formule est la suivante :
=RECHERCHEV(A12;[article.xls]BAIGNOIRE!$A$2:$K$73;2;FAUX)

BAIGNOIRE étant le nom de ma première feuille..... mais si je ne cherche pas une baignoire, ca ne fonctionne pas. Normal mais comment y remedier sans avoir une formule très lourde ,

Merci à tous

 

[martinette]

Re : RECHERCHEV dans plusieurs feuilles autre classeur

pour une meilleure compréhension de ma demande, je joins les deux fichiers excel

Merci par avance

 

[chris]

Re : RECHERCHEV dans plusieurs feuilles autre classeur

Bonjour

Si on reste sur une formule :

• soit rassembler les articles dans la même feuille et ajouter une colonne pour préciser ce que c'est
• soit dans le devis avec le type de l'article, par exemple type article (LAVABO) en colonne A et code en colonne B en et utiliser INDIRECT :
  =RECHERCHEV(B4;INDIRECT("[article.xls]"&A4&"!$A$2:$K$73");2;FAUX)
• soit sous réserve que les codes soit tous différents dans les types d'articles :
  =SI(NB.SI([article.xls]BAIGNOIRE!$A:$A;A4)>0;RECHERCHEV(A4;[article.xls]BAIGNOIRE!$A:$K;2;FAUX);SI(NB.SI([article.xls]LAVABO!$A:$A;A4)>0;RECHERCHEV(A4;[article.xls]LAVABO!$A:$K;2;FAUX);SI(NB.SI([article.xls]BIDET!$A:$A;A4)>0;RECHERCHEV(A4;[article.xls]BIDET!$A:$K;2;FAUX);"Code erroné")))

 

[martinette]

Re : RECHERCHEV dans plusieurs feuilles autre classeur

Merci beaucoup Chris, je tester tes solutions.
Bonne fin de wk

 

[Tibo]

Re : RECHERCHEV dans plusieurs feuilles autre classeur

Bonjour martinette, bises à chris,

La solution avec INDIRECT est celle à laquelle j'ai également pensé

Attention : elle nécessite que le fichier source soit ouvert

Pour pouvoir travailler avec un fichier fermé, il faut installer une macro complémentaire qu'on trouve sur le site de Laurent Longre :

Ce lien n'existe plus

Télécharger la macro complémentaire morefunc

Une fois installée, elle donne une fonction INDIRECT.EXT équivalente à INDIRECT, mais qui fonctionne avec les fichiers femés.

@+

 

[patte d'ours]

Re : RECHERCHEV dans plusieurs feuilles autre classeur

Même idée Que Chris et Tibo :

1) dans le fichier article, crée une nouvelle feuille que j’ai appelée Articles, reprenant la référence, l’intitulé et le nom exact de l’onglet où il faudra chercher.

2) Dans le fichier Devis, crée autant de zones nommées que tu as d’onglets dans le fichier article. Pour ce faire, Menu Insertion/Nom/Définir. Clique sur la flèche rouge de sélection et va avec la souris balayer les colonnes correspondantes dans le fichier Article. Il est préférable de sélectionner les colonnes pour ne pas avoir à modifier ensuite les zones nommées.

Dans ton exemple, j’ai nommé dans le fichier Devis, les zones BAIGNOIRE et LAVABO

3) Dans le fichier Devis, crée une nouvelle colonne qui avec un rechercheV va te permettre de récupérer ton nom d’index. Tu pourrais insérer cette formule dans les autres mais cela deviendrait très compliqué.

4) Modifie tes formules dans le fichier Devis. ATTENTION, tous les onglets doivent avoir la même structure de champs. Ce n’était pas le cas. Je t’ai donc supprimé la colonne J du fichier Devis

En espérant que cela correspondra à tes besoins.

Bonne fin de week-end

 

[martinette]

Re : RECHERCHEV dans plusieurs feuilles autre classeur

Merci vraiment à tous, ca marche !!!
Pate d'ours ton explication est très claire Bravo
cordialement

 

[cabu1980]

Re : RECHERCHEV dans plusieurs feuilles autre classeur

Bonjour à toutes et à tous,

J'ai une petite question concernant rechercheV sous Excel 2003(et oui encore...), ou plutôt sur la manière d'en améliorer le traitement, je m'explique :

- Je cherche à partir du classeur Devis une référence dans les feuilles du classeur Achats (dans les feuilles 1 à 3 et dans la feuille Codes)
- La première valeur cherchée est la désignation
- La seconde et le prix (HT pour l'instant, à calculer ensuite)

La fonction de recherche est la suivante :

=SI(A16<>"";SI(NB.SI([Achats.xls]Feuil1!$1:$65536;A16)>0;RECHERCHEV(A16;[Achats.xls]Feuil1!$1:$65536;2;FAUX);SI(NB.SI([Achats.xls]Feuil2!$1:$65536;A16)>0;RECHERCHEV(A16;[Achats.xls]Feuil2!$1:$65536;2;FAUX);SI(NB.SI([Achats.xls]Codes!$1:$65536;A16)>0;RECHERCHEV(A16;[Achats.xls]Codes!$1:$65536;2;FAUX);"")));"")

La fonction de calcul du prix TTC avec marge éventuelle est la suivante :

=SI(A16<>"";SI(NB.SI([Achats.xls]Feuil1!$1:$65536;A16)>0;ARRONDI.SUP(RECHERCHEV(A16;[Achats.xls]Feuil1!$1:$65536;3;FAUX)*tva*mp;0);SI(NB.SI([Achats.xls]Feuil2!$1:$65536;A16)>0;ARRONDI.SUP(RECHERCHEV(A16;[Achats.xls]Feuil2!$1:$65536;3;FAUX)*tva*mp;0);SI(NB.SI([Achats.xls]Codes!$1:$65536;A16)>0;RECHERCHEV(A16;[Achats.xls]Codes!$1:$65536;3;FAUX);"")));"")

Elles sont longues, mais fonctionnelles, grâce à la réponse de chris, merci )

Hélas, je dois faire face à de petit problèmes :

- Les temps d'apparitions des résultats est long, environ 1 seconde en saisie manuelle (par ex. 140247 ou m)
- Bien plus long si on copie/colle des valeurs prises dans le classeur Achats (par exemple de la feuille 2).
- Le classeur Achats doit rester ouvert (pas gênant mais je souhaiterais qu'il soit fermé) et doit être ouvert en premier chez moi en tout cas (bizarre )
- Lors de la modification du classeur Achats avec Devis ouvert, parfois (mais pas toujours) Excel recalcule à chaque modification, ça devient très vite casse-pieds.
- Si vous faites une copie (étirement vers le bas de la première cellule par ex.), le temps est long également.

Je pense que la formule de recherche multiple est la cause de la "lenteur", mais je ne vois pas comment réduire ces délais, je ne comprend pas l'utilisation de INDIRECT.EXT.
Il faut dire que je demande à Excel de rechercher sur la totalité de chaque feuille et non sur une plage stricte.

Les questions sont les suivantes :

Est-il possible de réduire ces délais (si possible sans tout changer sinon tant pis) ?
De garder le classeur Achats fermer ?
The question bête et/ou tarte, comment masquer la formule lors de l'utilisation du classeur Devis ?

Je sais, pour le calcul du prix, je peux ajouter/utiliser une colonne pour un calcul séparé dans les feuilles 1 à 3, mais je voulais me torturer un peu les neurones.
Pour les Feuilles feuill1 à 3 et Codes, je voulais tout simplement limiter le nombre de réf aux nombre de fournisseur (feuill1=fournisseur1, feuill2=fournisseur 2, etc. ...).

Malheuresement, je ne comprend pas grand chose aux macro, mais bon, si il le faut, pourquoi pas ....

Pour les pièces pièces jointes, les deux classeurs sont trop volumineux pour être joints, même compressés, ou alors, indiquez moi un une autre solution de mise à disposition.

Je vous remercie d'avance et bon été !!

P.S. : Ce forum es génial, nombreuses réponses et pédagogie, que demander de plus ?

 

[cabu1980]

Re : RECHERCHEV dans plusieurs feuilles autre classeur

Re-bonjour,

En limitant la plage de recherche de la fonction rechercheV, le délais à pu être écourté, néanmoins, je continue mes recherches pour le reste.

=SI(A16<>"";SI(NB.SI([Achats.xls]Feuil1!$A$9:$C$4000;A16)>0;RECHERCHEV(A16;[Achats.xls]Feuil1!$A$9:$C$4000;2;FAUX);SI(NB.SI([Achats.xls]Feuil2!$A$9:$C$4000;A16)>0;RECHERCHEV(A16;[Achats.xls]Feuil2!$A$9:$C$4000;2;FAUX);SI(NB.SI([Achats.xls]Codes!$A$9:$C$100;A16)>0;RECHERCHEV(A16;[Achats.xls]Codes!$A$9:$C$100;2;FAUX);"")));"")

Plage Feuil1!$A$9:$C$4000 -> soit de A9 à C4000
C'est mieux, mais peut être améliorable....

Je vous tiendrais au courant.

 

[BOISGONTIER]

Re : RECHERCHEV dans plusieurs feuilles autre classeur

Bonjour,

Le fichier article.xls est ouvert.

=RECHERCHEV(A2;INDIRECT(INDEX("[article.xls]"&nf;EQUIV(VRAI;(NB.SI(INDIRECT("[article.xls]"&nf&"!A2:A100");A2)>0);0))&"!A2:C100");2;0)
Valider avec Maj+ctrl+entrée

En colonne N
Noms des feuilles
baignoire
lavabo
bidet

Nom de champ
nf =DECALER(DEVIS!$N$2;;;NBVAL(DEVIS!$N:$N)-1)

JB
Formation Excel VBA JB

 

[Tibo]

Re : RECHERCHEV dans plusieurs feuilles autre classeur

Bonjour,

Pour avoir des plages de cellules à la bonne taille, il est possible de les nommer via la fonction DECALER

Ca peut donner ceci :

Insertion - Nom - Définir - Noms dans le classeur : plage_01 - Fait référence à :

=DECALER(Feuil1!$A$9;0;0;NBVAL($A$9:$A$10000);3)

Ensuite, dans tes formules, au lieu de préciser la plage de cellules, tu utilises ce nom.

Ca permet d'avoir des plages de cellules qui sont de la bonne longueur et gagner ainsi encore un peu de temps.

N'hésite pas à recréer un fichier simplifié de ton vrai fichier pour que tu puisse le joindre.

Sinon, il existe des sites comme cijoint.com qui permettent de joindre des fichiers de grande taille.

@+

Edit : Bing ! Désolé pour la collision Jacques

@+

 

[cabu1980]

Re : RECHERCHEV dans plusieurs feuilles autre classeur

Merci pour la rapidité des réponses

Je regarde de suite, voici les fichiers (merci Tibo ) :



BOISGONTIER, je vais regarder également ça de plus prés, pour les macro aussi, merci.

 

[cabu1980]

Re : RECHERCHEV dans plusieurs feuilles autre classeur

J'ai modifié le lien de chargement des fichiers, avec une mise à jour avec utilisation de plages nommées.

 

[cabu1980]

Re : RECHERCHEV dans plusieurs feuilles autre classeur

Bonjour,

C'est nickel avec les plages nommées, par contre je galère franchement sur le fait de conserver le classeur Achats fermer, j'ai essayé INDIRECT.EXT, je l'ai intégré à la rechercheV, mais sans résultats.

D'origine :

=SI(A16<>"";SI(NB.SI(Achat1;A16)>0;RECHERCHEV(A16;Achat1;2;FAUX);SI(NB.SI(Achat2;A16)>0;RECHERCHEV(A16;Achat2;2;FAUX);SI(NB.SI(Codes;A16)>0;RECHERCHEV(A16;Codes;2;FAUX);"")));"")

INDIRECT.EXT prend-t-il place dans le champ table_matrice de la rechercheV ? (C'est ce que j'ai essayé).

Je précise :
- Le classeur Achats et Devis sont/seront dans le même dossier
- Les plages de données des feuilles f1 à f3 et codes sont nommées respectivement achat1 à achat3 et codes, le tout sur une feuille (ref) du classeur Achats séparée

Qu'est-il possible de faire ?

Cijoint.fr - Service gratuit de dépôt de fichiers

 

[sapeur_alain]

Re : RECHERCHEV dans plusieurs feuilles autre classeur

Bonjour à tous,

Je viens déterrer ce topic qui m'a énormément aidé pour rechercher une date dans la totalité des feuilles d'un autre classeur nommé year2012.

J'ai utilisé la formule suivante :
{=INDEX(INDIRECT(INDEX("[year2012.xls]"&nf;EQUIV(VRAI;(NB.SI(INDIRECT("[year2012.xls]"&nf&"!G7:G1000");S14)>0);0))&"!F7:G1000");EQUIV(S14;INDIRECT(INDEX("[year2012.xls]"&nf;EQUIV(VRAI;(NB.SI(INDIRECT("[year2012.xls]"&nf&"!G7:G1000");S14)>0);0))&"!G7:G1000");0);1)}

où S14 contient le n° de série que je recherche parmi 2 colonnes de l'autre classeur : F contenant les dates et G les n° de série
et nf = DECALER(Feuil1!$W$4;;;NBVAL(Feuil1!$W:$W)-1) avec dans la colonne W le nom des onglets du classeur year2012.

Mon problème : les onglets du classeur year2012 contenant un espace ou un tiret ne sont pas reconnus, et ma recherche se solde donc par un #N/A. Lorsque j'enlève manuellement les espaces et tirets dans le nom des onglets et donc aussi dans ma liste de la colonne W les répertoriant, cela marche.

Auriez-vous des idées quant à comment contourner ce problème ? (avec des apostrophes, guillemets... ou autre)

Merci à tous,