XL 2016 #REF ! et nom de cellule existant sur cellules nommées

[Mak_tarmak]

Bonjour,
Je sollicite votre aide pour avoir votre avis sur ma méthode ou éventuellement voir comment vous auriez fait.

Mes collègues travaillent sur un fichier plan_de_formation.xlsx
Dans l'onglet plan_2023, les cellules de certains totaux ont été nommées. (ex: G36 = G36_Nbre_Stagiaires_Axe_I)
Ce fichier est régulièrement mis à jour.

Pour ne pas interférer sur leur fichier, je copie régulièrement leur onglet plan_2023 dans mon fichier bilan_2023.xlsx
Je me sers des cellules nommées (ex: G36 = G36_Nbre_Stagiaires_Axe_I) pour alimenter un onglet synthèse (ex : Total Stagiaires = G36_Nbre_Stagiaires_Axe_I + G127_Nbre_Stagiaires_Axe_II)

Ma problématique est lorsque je veux récupérer chaque semaine leur onglet plan_2023.
Je supprime l'onglet plan_2023 dans mon fichier (du coup j'ai des erreurs #REF! dans l'onglet synthèse) et j'importe le leurs (du coup j'ai une boite de dialogue qui m'indique que le nom existe déjà et me demande si je dois le garder ou pas car ils existent déjà dans le gestionnaire de noms).
Si je mets "oui" je me retrouve avec des doublons de nom de cellule dans le gestionnaire de noms et si je mets "non" il faut que je donne un nouveau nom (mais il y a beaucoup de cellules nommées).

J'ai voulu mettre une étendue Classeur sur chaque nom dans le gestionnaire de noms mais cela ne change rien.

Je n'ai pas d'inspiration pour savoir comment contourner ces #REF! et le fait que le nom existe déjà.

Auriez-vous d'autres pistes à me soumettre que je ne connaitrais pas ?

En vous remerciant pour votre aide,
Kad

 

[sylvanu]

Bonjour Mak_tarmak,

Je supprime l'onglet plan_2023 dans mon fichier (du coup j'ai des erreurs #REF! dans l'onglet synthèse)

Au lieu de le supprimer il vaudrait mieux l'effacer comme ça les plages nommées resteraient correctes .
( par un "clearcontents" et non un "clear", cela permet de conserver les plages nommées )
Puis y coller les données. Les formules resteraient ainsi intègres.

 

[Mak_tarmak]

Bonjour Sylvanu,
Je vais essayer cette méthode.
Pour le copier/coller des données, il y a une méthode spéciale en vba à utiliser ou bien wsSource.Copy Before:=wsDestination peut suffire ?
Merci,

 

[sylvanu]

wsSource.Copy Before:=wsDestination

Je ne suis pas sur de votre syntaxe pour copier des plages. Essayez :

Workbooks("Fichier Dest").Sheets("Feuille Dest").Range("Plage") = Workbooks("Fichier Source").Sheets("Feuille Source").Range("Plage").Value

Workbooks("Fichier Dest").Sheets("Feuille Dest"). est optionnel si la macro s'exécute à partir de cette feuille
Les deux plages doivent être identiques.

 

[Mak_tarmak]

Bonjour Sylvanu,
J'ai testé le ClearContents et cela marche nickel je n'ai plus d'erreur #REF! dans ma synthèse.
La problématique que ça a soulevé, c'est quand mes collègues ajoutent des lignes au plan (formations non prévues à la base). Par exemple, la cellule nommée G36 se retrouve sur la ligne 39.
Du coup, quand je copie ma plage de données, la cellule nommée reste en G36 sur la feuille de destination alors qu'elle s'est déplacée en G39 dans la feuille source.
Je pensais qu'en donnant des noms aux cellules, je comblerai le fait que des lignes peuvent être insérées en dessous ou au dessus.
Des sous-totaux nommés, il y en a beaucoup sur plusieurs lignes sur une plage allant de A15 à T220, je n'ai peut-être pas choisi la bonne méthode.
Ces cellules nommées alimentent ma synthèse mais je m'en sers aussi dans un tableau pour que les valeurs soient dynamiques et ce tableau me sert dans un TCD qui lui alimente un tableau de bord dynamique.

 

[sylvanu]

Bonjour,
Sans fichier test représentatif, cela ressemble à du chinois.

je n'ai peut-être pas choisi la bonne méthode.

Possible. cela dépend de la structure des deux fichiers.

Une autre approche est de ne pas utiliser une plage nommée mais de repérer la ligne désirée.
Par ex si en première colonne il existe un mot clé, ou autre.
On recherche ce mot puis on va chercher la donnée sur la bonne colonne.

Je pensais qu'en donnant des noms aux cellules, je comblerai le fait que des lignes peuvent être insérées en dessous ou au dessus.

Lorsqu'on nomme une cellule, ce nom suit la cellule qu'on insère colonnes ou lignes. Donc je ne comprends pas votre souci.
Si vous vous référez à une cellule nommée, vous la récupérerez correctement.

 

[Mak_tarmak]

Bonjour,
Sans fichier test représentatif, cela ressemble à du chinois.

Possible. cela dépend de la structure des deux fichiers.

Une autre approche est de ne pas utiliser une plage nommée mais de repérer la ligne désirée.
Par ex si en première colonne il existe un mot clé, ou autre.
On recherche ce mot puis on va chercher la donnée sur la bonne colonne.


Lorsqu'on nomme une cellule, ce nom suit la cellule qu'on insère colonnes ou lignes. Donc je ne comprends pas votre souci.
Si vous vous référez à une cellule nommée, vous la récupérerez correctement.

Merci Sylvanu.
Je vais essayer de vous préparer un fichier représentatif dans la matinée.
En fait dans la feuille de destination la cellule nommée est bien en G36 après le ClearContents.
Mais comme mes collègues ont rajouté des lignes avant, la cellule nommée G36 se trouve en ligne 39 au lieu de 36 et quand je copie la plage de données du fichier source vers le fichier de destination, la cellule nommée est toujours en G36 après le collage des données. J'ai fait une copie manuelle pour tester, j'ai peut-être choisi la mauvaise options de collage.

 

[sylvanu]

la cellule nommée est toujours en G36 après le collage des données.

Si la cellule G36 est nommée "toto". Si vous insérer trois lignes;, la cellule nommée "toto" sera en G39.
Le nommage suit la cellule.
Donc y a un truc qui m'échappe dans votre explication.
Un ex pour illustrer mes propos :

 

[Mak_tarmak]

Je suis bien d'accord avec vous. Dans le fichier source de mes collègues, la cellule nommée "toto" se décale bien en G39 si l'on insère des lignes.
Le soucis est dans le fichier de destination. Comme généralement le fichier de destination est celui de la semaine d'avant, la cellule "toto" est encore en G36 même après un ClearContents.
Entretemps mes collègues ont pu ajouté des lignes ("toto" en G39"). Du coup je copie la plage de données de la semaine actuelle vers la semaine - 1 ("toto" en G36) et quand je colle les données dans le fichier de destination, "toto" est toujours en G36 et il ne prend pas le fait qu'il ait été déplacé en G39.
Je pourrai faire un Clear sur le fichier de destination comme ça il prendra en compte les déplacements des cellules nommées sauf que cela me fait "sauter" tous les références (#REF!) dans ma synthèse.
Je vous prépare deux fichiers ce sera mieux, j'ai du mal à être clair.

 

[sylvanu]

Alors la seule solution qui vous reste à mes yeux est de calculer où se trouve cette donnée.
Elle peut être par ex à droite due la cellule qui contient "Valeur", ou encore dans la colonne dont l'entête est "Cette semaine".
Une autre approche, si toutes les cellules sont nommées, est de ne copier que celles ci.
Par ex si vous copiez la cellule nommée Toto, alors où qu'elle soit elle sera bien copiée collée.

 

[Mak_tarmak]

Alors la seule solution qui vous reste à mes yeux est de calculer où se trouve cette donnée.
Elle peut être par ex à droite due la cellule qui contient "Valeur", ou encore dans la colonne dont l'entête est "Cette semaine".
Une autre approche, si toutes les cellules sont nommées, est de ne copier que celles ci.
Par ex si vous copiez la cellule nommée Toto, alors où qu'elle soit elle sera bien copiée collée.

Pour illustrer mon propos j'ai fait deux fichier.
Le fichier source c'est celui que mes collègues enrichissent chaque semaine.
Ils ont rajouté deux stages en L6 et L14.
Les cellules nommées sont maintenant en D9 et D15.

Le fichier destination c'est le fichier dont je me sers pour faire d'autres traitements et que j'actualise chaque semaine.
Je veux récupérer le plan réactualisé du fichier source pour le mettre dans mon fichier mais dans mon fichier les cellules nommées sont en D8 et D13.
De plus; les cellules nommées ont une référence dans l'onglet Synthèse.

Si je fais un Clear, je peux copier mon nouveau pln tranquillement mais les références de la synthèse sautent et je dois corriger les #REF!
Si je fais un ClearContents, les cellules nommées de mon fichier restent en place mais celles de mes collègues ont bougé et ne vont pas au bon endroit dans mon fichier.

En écrivant tout ça, j'ai pas l'impression d'être beaucoup plus clair.

 

[sylvanu]

Question stupide : Pourquoi ne pas rechercher "Ss-total I" et "Ss-total II" plutôt que de passer par des plages nommées ?
En faisant :

=INDEX(Plan_2023!A1:$Z$1000;EQUIV("Ss-total I";Plan_2023!$A:$A;0);EQUIV("Nbre stagiaires";Plan_2023!$1:$1;0))
et
=INDEX(Plan_2023!A1:$Z$1000;EQUIV("Ss-total II";Plan_2023!$A:$A;0);EQUIV("Nbre stagiaires";Plan_2023!$1:$1;0))

On peut rajouter lignes et colonnes sans problème.
Le seul impératif est qu'en ligne 1 on trouve "Nbre stagiaires", et en colonne 1 "Ss-total I".
Voir PJ.

 

[Mak_tarmak]

Question stupide : Pourquoi ne pas rechercher "Ss-total I" et "Ss-total II" plutôt que de passer par des plages nommées ?
En faisant :

=INDEX(Plan_2023!A1:$Z$1000;EQUIV("Ss-total I";Plan_2023!$A:$A;0);EQUIV("Nbre stagiaires";Plan_2023!$1:$1;0))
et
=INDEX(Plan_2023!A1:$Z$1000;EQUIV("Ss-total II";Plan_2023!$A:$A;0);EQUIV("Nbre stagiaires";Plan_2023!$1:$1;0))

On peut rajouter lignes et colonnes sans problème.
Le seul impératif est qu'en ligne 1 on trouve "Nbre stagiaires", et en colonne 1 "Ss-total I".
Voir PJ.

Bonjour Sylvanu, c'est loin d'être une question stupide, je n'y ai tout simplement pas pensé car je ne maitrise la fonction index/equiv.
La solution me plait bien mais est-ce que je pourrai l'appliquer à mon fichier ?, j'ai vraiment simplifié l'exemple mais j'ai l'impression que oui.
J'ai des titres en ligne 13 de la colonne F à Q et eux ils ne bougent pas de lignes, ni de colonnes (Nbre stagiaires distanciels, Nbre stagiaires présentiels, Nbre jours ...)
Ce sont les sous-totaux qui bougent uniquement de lignes. Dans le fichier d'origine, il y a 3 plages de sous-totaux : L36, L127 et L182 pour les colonnes de F à Q.
Si vous pouvez m'expliquer la formule, je pense pouvoir l'adapter facilement.
Une question me vient. Avec cette formule, que dois-je faire des données que j'importe régulièrement dans plan_2023 : un Clear et je colle tout, un ClearContents ou bien bien je supprime la feuille et importe la nouvelle ?
Je suppose que votre formule ne fera pas de #REF! si les cellules nommées ne sont pas présentes le temps de la copie. Merci pour vos conseils.

 

[sylvanu]

Bonjour,
Quelques explications en PJ, en espérant que cela soit suffisamment clair.

 

[Mak_tarmak]

Bonjour,
Quelques explications en PJ, en espérant que cela soit suffisamment clair.

Les explications sont tellement bien présentées dans le fichier que cela ne peut être que clair, voire très clair. J'ai tout compris et je trouve cette fonction index/equiv géniale. J'ai pu qu'à mettre en application car j'ai une soixantaine de cellules nommées.
En tout cas, un grand merci pour votre aide et votre patience. Je suis content d'avoir encore appris quelque chose.
Bonne continuation à vous et qui sait à bientôt.