XL 2019 Liaison avec un second fichier excel

[Electronull]

Bonsoir à tous,

J'ai un fichier excel qui a 1 feuille qui contient un tableau avec 3 colonnes : Intitulé; prix; catégarie
Dans mon second fichier, j'ai 1 onglet par jour calendrier, avec des colonnes : Description; prix; catégorie et autre
Dans la colonne prix j'ai la formule =+SI(ESTVIDE(D6);" ";RECHERCHEV(D6;'[Donnees_Liste_Produits.xlsx]Liste des produits'!$A$1:$C$799;2;0))
Le souci, j'ai la référence au tableau $A$1:$C$799 qui ne suit pas le maximum de lignes si je rajoute des produits dans mon tableau , ici 799 alors que j'ai 810 lignes

Comment faire pour que le nbre de lignes se met à jour automatiquement ?

Merci pour votre aide

 

[chris]

Bonjour

Soit utiliser des références aux colonne entières, soit mieux utiliser PowerQuery plutôt que des liaisons externes par formule

 

[Electronull]

Salut Chris,
Merci pour ta réponse
Je pensais que comme c'était un tableau que ça fonctionnait même si j'ajoutais des lignes.

 

[Nain porte quoi]

Hello,

si le "tableau" qui est dans "[Donnees_Liste_Produits.xlsx]Liste des produits" est un tableau structuré les références devraient ressembler à ça Tableau1[[nom_col_1]:[nom_col_2]] et alors ça suit le nombre de ligne

 

[Electronull]

Salut,

Oui, mais comment je renseigne le chemin où se trouve le fichier de données

Ma formule =+SI(ESTVIDE(D6);" ";RECHERCHEV(D6;'[Donnees_Liste_Produits.xlsx]Liste des produits'!$A$1:$C$799;2;0))

le titre des colonnes dans le fichiers liste des produits
Intitulés Prix Catégorie Colonne1 Colonne2 Colonne3

Mon tableau T_Produits va de A1 à F809 (pour l'instant)

Donc, je dois mettre quoi dans ma formule, je galère un peu avec ça

Merci

 

[Electronull]

Bon, j'ai rectifiéune erreur de ma part pour lea référence au tableau de données
J'avais mis le nom et nom la plage qui est T_Produits
=+SI(ESTVIDE(D6);" ";RECHERCHEV(D6;Donnees_Liste_Produits.xlsx!T_Produits[#Données];2;0))

MAIS comme vous le voyez, j'ai entre crochets [#Données] c'est quoi ce trucs ?

Ca fonctionne mais j'aimerais savoir pourquoi il y a ceci

Merci

 

[AtTheOne]

Bonjour à toutes et à tous, bonjour @Electronull
Malheureusement les références aux adresses structurées ne sont pas interprétées quand le fichier source est fermé (on obtient alors des #REF!)
Par contre, un nom défini faisant référence à une plage de cellules (du style feuil1!$A$2:$G$16) est lui interprété même si le classeur source est fermé.
La solution consiste à ajouter une petite ligne dans l'événement Worksheet_Change de la feuille contenant le tableau structuré pour mettre à jour un nom défini (disons "MaSource") qui couvre le TS à chaque fois que celui ci est modifié :

Si ton tableau structuré s'appelle "tb"

Private Sub Worksheet_Change(ByVal Target As Range)

     If Not Intersect([tb], Target) Is Nothing Then ThisWorkbook.Names.Add Name:="MaSource", RefersTo:=[tb]

End Sub

Tu ouvres les deux fichiers et dans le fichier cible tu places des formules du style

=RECHERCHEV(A2;WbkSOURCE.xlsm!MaSource;4;FAUX)

"WbkSOURCE.xlsm" étant le nom du classeur contenant le tableau structuré "tb"

Voir les fichiers joints

À bientôt

 

[chris]

Salut Chris,
Merci pour ta réponse
Je pensais que comme c'était un tableau que ça fonctionnait même si j'ajoutais des lignes.

MAIS comme vous le voyez, j'ai entre crochets [#Données] c'est quoi ce trucs ?

Manifestement tu ne maîtrises pas les tableaux structurés qui existent pourtant depuis plus de 20 ans dans Excel

Tableaux structurés Excel

Tableaux structurés Excel

fauconnier.developpez.com

Déjà dit : la meilleure solution passe par PowerQuery qui existe depuis 2010...

 

[Nain porte quoi]

Dernier conseil : un tableau structurée s'agrandit au-to-ma-ti-que-ment donc inutile de prévoir 799 lignes quand une dizaine suffisent.

@chris, si ça fais 20 ans, tu pourrais remettre à jour ta signature alors 😉

 

[AtTheOne]

Re,
Reste le problème que j'évoque dans le post #7, quand on ferme le fichier source, si l'on utilise le nom du tableau, on obtient des #REF! lors du recalcul...
D' où ma proposition de mise à jour d'un nom défini qui se réfère à une adresse (celle du TS)
À bientôt

 

[AtTheOne]

si ça fais 20 ans, tu pourrais remettre à jour ta signature alors

@chris parle des TS quand il dit que cela existe depuis plus de 20 ans, pas de PowerQuery comme dans sa signature...🤣

 

[Electronull]

Re,
Merci pour tes fichiers AtTheOne, j'ai la même chose, pourtant je dois quand même ouvrir mon fichier source pour avoir les articles dans ma liste déroulante dans le fichier cible.
Je cherche

Merci

 

[job75]

Bonsoir à tous,

@chris parle des TS quand il dit que cela existe depuis plus de 20 ans

Il faut savoir que chris est la reine des TS et des TCD.

Bonne nuit.

 

[AtTheOne]

Bonjour à toutes & à tous.

chris est la reine

Excuse-moi @chris, je ne savais pas 🙄
Merci @job75 pour l'info 👌
À bientôt

 

[AtTheOne]

Re

pourtant je dois quand même ouvrir mon fichier source pour avoir les articles dans ma liste déroulante dans le fichier cible

Cette solution utilise les formules matricielles dynamiques disponibles dans les version 2021 et ultérieures.
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
La parade est un peu la même qu'au post#7 : Définir dans la classeur source un nom qui fait référence, par une adresse, à ta liste de choix.

En supposant que la liste de choix soit la colonne "Col1" du TS "tb" de la feuille "Données".

J'ai ajouté une feuille "Tables" au fichier source "WbkSOURCE b.xlsm" avec une liste des choix possibles (formule matricielle dynamique =TRIER(UNIQUE(tb[col1]) par exemple) dans une cellule nommée "Liste" (ici $A$2).
On obtient une liste triée des valeurs uniques de Col1. (alors Liste# fait référence au résultat de la formule matricielle dynamique.

Ensuite j'ai ajouté une ligne à la macro Worksheet_Change de la feuille de données :

Private Sub Worksheet_Change(ByVal Target As Range)

     If Not Intersect([tb], Target) Is Nothing Then ThisWorkbook.Names.Add Name:="MaSource", RefersTo:=[tb]
     If Not Intersect([tb[col1]].EntireColumn, Target) Is Nothing Then ThisWorkbook.Names.Add Name:="MaListeSource", RefersTo:=[Liste#]

End Sub

Cette ligne de code met à jour le nom défini "MalisteSource" à chaque changement dans la colonne "Col1" (on surveille tout changement dans la colonne entière en cas de suppression de ligne en fin du tableau "tb")

Dans le fichier cible j'ai créé une feuille "Tables" dans une cellule de laquelle (ici $A$2) je fait référence au nom "MaListeSource" du classeur "WbkSource b.xlsm" (='WbkSOURCE b.xlsm'!MaListeSource).
J'ai nommé cette cellule "MaListeCible"

Maintenant dans les cellules où je veux une liste de choix j'ai mis une validation de données, Autoriser : liste, source : =MaListeCible#

Cette manipulation, un peu élaborée, permet de conserver la liste de choix même quand le fichier source est fermé.

Remarque : lorsque l'on ouvre le fichier cible Excel émet un message avertissement informant l'existance de liaison extérieures, ce qui est normal, accepter la mise à jour.

Pour toi utiliser mon exemple, il faudra mettre à jour les liaisons en modifiant la source (car actuellement ces liaisons pointent sur un de mes répertoires 😉

Voilà, bon courage et à bientôt