concatenation adresse suivant userId pour mise à jour formule vlookup

Version imprimable

20/03/2020, 16h00
bruno37

concatenation adresse suivant userId pour mise à jour formule vlookup

Bonjour,

J'utilise une formule "Vlookup" pour aller cherche des prix dans un fichier en fonction d'une référence de produit saisie.
Jusqu'à aujourd'hui pas de problème, les fichiers étaient stocker sur un lecteur réseau physique mais maintenant les fichiers sont stocker sur le cloud via SharePoint.
Une synchronisation est faite sur le lecteur "C" de chaque utilisateur, mais comme je suis à l'origine de la mise à jour de la liste des prix lorsque je ferme et enregistre le fichier l'adresse m'est attribuée.
Mon User est "YE8792":

Code:

VLOOKUP(AW11;'C:\Users\ye8792\CS GM2 Europe All Teams - Orleans Hub Projects\Price list\[COR_FR_20200306.xlsb]output_FR'!$A:$C;3;FALSE)

le problème est que quand un autre User ouvre ce fichier qui ce sera synchronisé sur leur PC le User dans l'adresse ne sera pas la bonne dans la formule.
J'ai donc récupéré à l'ouverture le "User name" via VBA dans une cellule que j'ai nommée 'UserId" à l'ouverture du fichier et J'ai essayé de concaténer avec la fonction "CONCAT(" mais ca ne marche pas.

Code:

VLOOKUP(AW11;CONCAT("'C:\Users\";UserId;"\CS GM2 Europe All Teams - Orleans Hub Projects\Price list\[COR_FR_20200306.xlsb]output_FR'!$A:$C");3;FALSE)

J'ai essayé de cette manière aussi mais cela ne fonctionne pas non plus.

Code:

"'C:\Users\" & UserId & "\CS GM2 Europe All Teams - Orleans Hub Projects\Price list\[COR_FR_20200306.xlsb]output_FR'!$A:$C"

Auriez vous une solution afin que le fichier puisse mettre à jour automatiquement les formules à l'ouverture par n'importe quel user?
Merci
20/03/2020, 16h23
Menhir

Pour transformer une chaine de caractères en véritable adresse, il faut utiliser la fonction INDIRECT().
Lire ça : https://support.office.com/fr-fr/art...1-92b6306fa261
23/03/2020, 09h23
bruno37

erreur avec le fonction Indirect()

Bonjour ,

J'ai déjà essayé avec la fonction "indirect()" mais un message d'erreur "#REF"

Code:

=VLOOKUP(AW11;INDIRECT("'C:\Users\" & UserId & "\Sandvik\CS GM2 Europe All Teams - Orleans Hub Projects\Price list\[COR_FR_20200306.xlsb]output_FR'!$A:$C");3;FALSE)
23/03/2020, 10h49
Menhir

J'avais oublié de tenir compte du fait que tes données sont dans un autre fichier.
INDIRECT() ne fonctionne pas pour les données extérieures du fichier où elle se trouve.

On ne le répètera jamais assez : Excel n'est pas doué pour les multi-fichiers, multi-utilisateurs et autres multi-tâches.
Quand on se retrouve dans ce tels cas, il est fortement conseillé de passer sur un vrai SGBD ou, au pire, d'utiliser des outils de SGDB comme PowerQuery.
Lire ça : https://excel.developpez.com/actu/27...re-Fauconnier/
01/04/2020, 11h55
bruno37

power query?

Merci pour la réponse mais , là j'avoue je suis complètement perdu.
01/04/2020, 12h01
Pierre Fauconnier

Salut.

Tu amènes toi-même la seule réponse valable à donner à ton problème: importer tes données externes dans ton fichier et travailler en local. C'est une mauvaise pratique que d'utiliser des formules inter-fichiers. Et puisque tu es en Office 365, tu peux (dois?) utiliser Power Query pour rapatrier tes données. Après, créer le lien direct par Power QUERY lors de l'importation me semble la meilleure idée, car ce sera plus rapide que RECHERCHEX (365), RECHERCHEV ou INDEX/EQUIV.

Suis le lien vers mon billet de blog dans la réponse de Menhir (Merci Menhir ;) ). Il montre comment récupérer des donnés externes via Power Query. Par contre, il faudra problement recompser le lien dans PowerQuery.

Remarque générale: il faut bannir systématiquement les formules interclasseur!
01/04/2020, 13h13
Menhir

Citation:

Envoyé par bruno37

Merci pour la réponse mais , là j'avoue je suis complètement perdu.

C'est normal : tu es en train d'essayer de faire la plomberie d'une maison équipé d'un simple couteau suisse.
Excel n'est pas l'outil correspondant à ton besoin : je le répète : Excel n'est pas fait pour tout ce qui est "multi"

Si tu n'as rien d'autre ou que tu veux absolument utiliser Excel, il va falloir complètement modifier ton besoin ou la stratégie pour l'atteindre, comme le suggère Pierre Fauconnier.
01/04/2020, 13h39
Pierre Fauconnier

Je ne serais pas aussi catégorique que Menhir pour ce qui est d'utiliser (ou pas) Excel dans ce genre de cas. POWER QUERY apporte justement le maillon manquant pour permettre à Excel d'agréger des données de sources multiples au sein d'un même classeur. Ce qui pose problème ici, ce n'est pas le multi-fichiers, mais le "multi-chemins" pour y arriver, et c'est un problème qui n'a RIEN à voir avec Excel.

Par contre, je pense qu'il aurait été intéressant que, sur tous les postes de ton organisation, ce soit le même chemin pour arriver à Sharepoint. Sans cela, c'est effectivement un peu plus casse-tête. Moi, par exemple, j'ai rerouté mon OneDrive sur un autre disque que C:\ et dans un autre dossier que le dossier UserTrucMuche.

A mon avis, dans un premier temps, il serait intéressant d'investiguer de ce côté-là. Perso, je n'ai jamais compris comment c'était possible que les informaticiens réseaux et système d'une boite ne pensent pas à ce genre de trucs et aux problèmes qui vont immanquablement découler du fait d'avoir des chemins différents pour les utilisateurs.

Si vraiment il n'y a pas moyen de faire autrement, j'exigerais au moins que l'on mappe une même lettre pour tous les utilisateurs vers leur dossier Sharepoint local. De ce fait, on pourrait avoir par exemple le chemin M:\ installé sur chaque poste et qui pointe où il faut. Ainsi, les liens, qu'ils soient Power Query ou autre, pointent tous vers M:\... et le problème est résolu.

Ce n'est normalement pas à l'utilisateur Excel de pallier les manquements et erreurs des admins système. Et si ce problème n'est pas réglé et que je chemin n'est pas rendu unique, POWERQUERY rencontrera le même problème que ton RECHERHCHEV (mais il sera plus facile à résoudre).

Cela étant dit, ça ne change rien à ce que j'ai dit auparavant: Même avec le même chemin, il faut bannir les formules interclasseur et ramener les sources multiples via[/I] PowerQuery dans un seul classeur pour les traiter et les analyser.
01/04/2020, 14h11
Menhir

Citation:

Envoyé par Pierre Fauconnier

POWER QUERY apporte justement le maillon manquant pour permettre à Excel d'agréger des données de sources multiples au sein d'un même classeur.

C'est l'une des pistes comprises dans mon "revoir la stratégie", comme explicitement précisé dans ma première réponse. ;)
01/04/2020, 14h30
bruno37

power query?

Alors j'ai essayé d'importer les données via PowerQuery, j'ai maintenant une nouvelle feuille avec toutes les données que j'ai pu trier dans PowerQuery.
Seulement voilà, mon fichier faisait 6Mo et il fait maintenant 23Mo. j'ai importé presque 65000 lignes.
Ce n'est absolument pas ce que je voulais. c'est pour cette raison que j'utilisais les formules entre fichiers pour ne pas avoir cette taille de fichier.
01/04/2020, 14h50
Pierre Fauconnier

Citation:

Envoyé par Menhir

[...]Excel n'est pas l'outil correspondant à ton besoin : je le répète : Excel n'est pas fait pour tout ce qui est "multi"[...]

C'est par rapport à cette remarque que je réagissais, car PowerQuery est justement fait pour tout ce qui est multi. C'est sa principale raison d'être ;)

Citation:

Envoyé par bruno37

[...]
Seulement voilà, mon fichier faisait 6Mo et il fait maintenant 23Mo. j'ai importé presque 65000 lignes.[...]

Un nouvel outil, ça s'apprend. Tu n'es pas obligé d'importer toutes les données dans ton classeur. Tu te sers de PowerQuery pour ce dont tu as besoin. Tu importes tes données dans PowerQuery, tu joins tes tables (l'équivalent de RECHERCHEV) et tu ne ramènes dans Excel que le résultat traité par PowerQuery. Si tu dois travailler tes données dans un TCD par exemple, tu n'es pas obligé de les importer dans ton classeur, tu peux directement connecter ton tcd à une de tes requêtes dans Power Query. Tu limites donc le surpoids.

Idéalement, dans ce cas de figure où j'ai un classeur A qui doit aller chercher des données dans un classeur B (dans ton cas, A va vers B via RECHERCHEV), je crée un classeur C qui est mon classeur d'analyse et qui récupère les données traitées d'un PowerQuery qui va chercher les infos dans A et dans B et qui les agrège. Ainsi, le classeur C ne contient que les données traitées finales.

Pour ce qui est de la taille du fichier, tu as un problème similaire lorsque tu crées un TCD, qui fait lui aussi grossir ton fichier. Personnellement, je ne trouve pas qu'un fichier de 23MO est un gros fichier, mais si tu réduis les données à l'import avec PowerQuery, tu pourras le faire un peu maigrir. De toute façon, tu ne peux pas avoir le beurre, l'argent du beurre et le sourire de la crémière. Soit tu t'amuses à remplacer (par CTRL+H à la main ou par macro), les parties de formule des RECHERCHEV concernés par ton problème (pour moi, c'est du bidouillage qui ne tient pas la route), soit tu travailles sur base d'une architecture professionnelle avec PowerQuery. il n'y a pas des masses d'autres possibilités.
07/04/2020, 14h47
bruno37

Désolé, je ne vois pas comment faire, je comprend pas bien comment lier la requête à mon tableau