Discussion :

[mia73]

Bonjour,


je vais essayer de vous exposez mon pb (qui n'est pas si compliqué en soi mais pas évident à expliquer) le plus clairement possible, merci d'avance pour votre lecture

Je travaille dans un classeur possédant plusieurs feuilles de calcul, chacune comprenant un tableau de structure identique mais avec des données différentes selon l'année (feuille 1 = données de l'année 2001, feuille 2 = 2002, etc.). Une dernière feuille constitue ma feuille BILAN. J'y construit un petit tableau bilan pour chacune des années. Dans ces tableaux je calcule diverses choses (moyennes, etc.) grâce à des formules faisant appel aux listes nommées que j'ai défini dans chaque feuille. Une liste nommée correspond à une colonne : REF_2001, CONSO_2001, ... pour la feuille 1 ; REF_2002, CONSO_2002, ... pour la feuille 2, etc.

Là je viens de terminer le tableau de l'année 2001 et je souhaiterais à présent le copier/coller pour l'année 2002 (puisque les formules sont exactement les mêmes pour chaque année). Seules les plages nommées changent. C'est là que j'ai besoin de votre aide. Car pour le moment j'en serais réduite à changer tous les "2001" en "2002" des plages nommées dans chacune de mes formules... ce qui n'est vraiment pas pratique !
Pour me faciliter la vie et surtout gagner du temps, je pensais stocker les noms de mes listes de données dans une cellule. Je n'aurais ainsi qu'à changer une seule fois chaque "2001" en "2002" et mes formules appelleraient ensuite directement cette cellule. Seulement, lorsque je rentre le nom de ma liste de données dans cette cellule en tant que texte, ma formule ne le reconnait pas comme la liste nommée. Et lorsque je le rentre en tant que liste nommée ({=REF_2001}), ma cellule ne prend que la première valeur de la liste. Est-ce que vous sauriez donc comment stocker toute la liste dans cette unique cellule ? Ou bien auriez-vous une autre idée pour mon problème ?


En espérant que vous avez bien suivi la chose,

Merci d'avance pour votre attention et vos idées !

[GerardCalc]

Bonjour,

Penches-toi (sans tomber ) sur la fonction INDIRECT.

Exemple :

Dans tes formules, remplaces la référence de plage REF_2001 par
INDIRECT("REF_"&A1) la cellule A1 contenant 2001.

Pour changer la référence dans la formule, il te suffit alors de changer le contenu de A1 (2001→2002...etc). Tu peux même utiliser une liste déroulante en A1.

A+

[mia73]

En me penchant sur la fonction INDIRECT, je suis retombée sur mes deux pieds
C'est parfait, merci beaucoup !

[mia73]

Tout marche bien, seulement je rencontre un problème lorsque je passe à une plage dynamique. Jusqu'à présent, j'avais défini mes plages de données de façon fixe (par exemple ='Feuille 1'!$A$2:$A$500). Mais pour prendre en compte d'évenuels ajouts de lignes, j'utilise à présent la formule suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=DECALER('Feuille 1'!$A$1;1;0;NBVAL('Feuille 1'!$A:$A)-1;1)

Et à partir de ce moment, la formule avec INDIRECT ne fonctionne plus. Elle ne semble plus reconnaître la plage de données et m'affiche #REF!
(Par contre cela marche avec l'ancienne méthode "à la main", en tapant directement le nom de la plage de données)

[mia73]

Après quelques recherches sur le net, il s'avère qu'apparament il est impossible d'utiliser INDIRECT avec des plages dynamiques faisant appel à la fonction DECALER... Peut-être quelqu'un a-t'il une solution alternative (soit une autre façon de rendre la plage dynamique, soit une fonction équivalente à INDIRECT...) ?

En attendant et bien je vais repartir sur mes plages fixes !

[Pierre Fauconnier]

Salut.

Tu peux recomposer l'adresse à passer à INDIRECT(). Je suppose que la structure de ta feuille est la même d'année en année et qu'elle est fixée de façon "définitive"...

Si c'est le cas, tu peux travailler sur les colonnes en direct. Avec le nom de la feuille en A2, tu pourras utiliser un "indirect dynamique",. Inspire-toi de la formule suivante, qui utilise le nom de la feuille en A2 pour calculer la somme des données reprises en A de cette feuille, de la deuxième ligne du tableau à la dernière ligne utilisée en A

=SOMME(INDIRECT("'" A2 & "'!a2:a" & NBVAL(INDIRECT(A2 & "!a:a"))-1))

Si la structure devait se modifier d'année en année par l'ajout ou le retrait de colonnes d'année en année amenant les intitulés à ne pas être toujours dans les mêmes colonnes, ou que la structure de ton récapitulatif ne suivait pas celle des feuilles annuelles, tu pourrais te baser le nom de l'intitulé pour retrouver sa colonne avec EQUIV... Cela donnerait évidemment une formule (nettement!)plus longue, mais générique et pas vraiment plus complexe.

Dis ce qu'il en est.

[mia73]

Bonjour,

merci pour les infos ; mais je rencontre quelques soucis avec la formule... Excel me dit qu'elle comporte une erreur. J'ai essayé de regarder dans les guillemets et les parenthèses mais je n'ai pas réussi à la trouver.

En tout cas oui, la structure des feuilles est fixée et est la même d'année en année. Par contre la feuille bilan a une structure différente, mais je ne vois pas en quoi cela impliquerait d'utiliser la fonction EQUIV ; pour moi la formule que tu m'as proposée conviendrait tjs. Mais peut-être est-ce parce que je ne l'ai pas si bien comprise que ça...

[vieuxmonsieur]

=SOMME(INDIRECT("'" A2 & "'!a2:a" & NBVAL(INDIRECT(A2 & "!a:a"))-1))

L'erreur ne viendrait-elle pas de cet apostrophe ?

[GCASPIC10]

Celà ne correspond pas à 100% à ton sujet, mais j'ai réglé le problème d'ajout de ligne dans une feuille excel avec exploitation de la zone concernée dans VBA de cette façon. On attendant de connaître la réponse à ma question

http://www.developpez.net/forums/d93...ble-excel-vba/

[Pierre Fauconnier]

Le vieux monsieur a encore une bonne vue...

Il manquait un signe & dans ma formule

=SOMME(INDIRECT("'" & A2 & "'!a2:a" & NBVAL(INDIRECT(A2 & "!a:a"))-1))

J'ai mis les apostrophes pour permettre l'utilisation de noms d'onglets contenant des espaces...

[mia73]

Malheureusement cela ne fonctionne toujours pas : l'analyse du calcul montre que INDIRECT(A2 & "!a:a") renvoit #REF! ...
En A2 j'ai stocké le nom de ma feuille sous forme de texte et la colonne A comporte des nombres.

avez-vous une idée d'où pourrait venir le problème ?

[GerardCalc]

En mettant aussi les apostrophes dans le deuxième INDIRECT :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMME(INDIRECT("'" & A2 & "'!a2:a" & NBVAL(INDIRECT("'" & A2 & "'!a:a"))))

J'enlèverais aussi le -1 par la même occasion.

A+

[mia73]

merci beaucoup

finalement ça ne va pas vraiment dans mon cas, car ayant déjà des formules relativement longues cela alourdit beaucoup mes cellules. D'autre part je dois changer à chaque fois manuellement les lettre des colonnes (mes différentes formules appellent jusqu'à 4 colonnes différentes).
Je pensais pouvoir stocker le

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

INDIRECT("'" & A2 & "'!a2:a" & NBVAL(INDIRECT("'" & A2 & "'!a:a")))

dans une cellule, mais en fait cela ne représente toujours pas la plage de données, seulement la valeur de la première cellule. Cela dit merci quand même, c'est toujours bon à prendre et ça me servira sûrement pour une autre fois !