Discussion :

[RJSCVF]

Bonjour à tous !
J'ai besoin d'aide pour imbriquer la fonction suivante qui me donne une erreur de référence #Ref!

Code formule :

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

=DECALER($A$2;;;NB.SI(D_AnnééCompta;"?*"))

Le but est de m'en service pour rendre dynamique une zone de liste déroulant en la collant dans l'espace de formule de ma zone de nom.

Merci

[Patrice740]

Bonjour,

Syntaxe : DECALER(réf;lignes;colonnes;[hauteur];[largeur])

la hauteur (NB.SI) ne doit pas être égal à 0

[Philippe Tulliez]

Bonjour,

Le but est de m'en service pour rendre dynamique une zone de liste déroulant en la collant dans l'espace de formule de ma zone de nom.

La fonction DECALER utilisée pour rendre dynamique une liste déroulante date de l'époque de la version 2003 d'excel.
Depuis la version 2007, la conversion d'une plage de données en tableau structuré permet de rendre dynamique une liste déroulante, il suffit de nommer la plage de cellules en faisant référence à la partie données de la liste.

[RJSCVF]

Merci pour vos réponses.

Au fait, ma zone de liste est sur la colonne A et s'étend de la cellule A1 à A21. Toutes ces cellules ont cette formule : =SIERREUR(INDEX(AnneCompta;EQUIV(0;INDEX(NB.SI($A$1:A1;AnneCompta););0));"") qui extrait sans doublon les années comptable d'une autre table en partant de 2019. Puisque je compte utiliser ce classeur pour plusieurs années.

Cette zone sans doublon c-à-d de la colonne A1 à A21, je l'ai nommée : D_AnnééCompta. C'est donc à partir de ce nom (Zone nommée) que j'essaie de créer une liste déroulante dynamique avec la formule suivante :

Code formule :

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

=DECALER($A$2;;;NB.SI(D_AnnééCompta;"?*"))

NB : Cette formule marche bien dan une vidéo que j'ai suivie, mais chez moi elle me donne une erreur de référence #REF! avec ce message :

Déplacer ou supprimer des cellules provoque une référence de cellule non valide, ou la fonction renvoie une erreur de référence.

Je ne parviens pas à corriger la formule pour avoir le résultat escompté.

J'espèce que ces détails éclairent mieux sur ce problème !

Merci

[Philippe Tulliez]

Bonjour,
Comme je l'ai écrit dans ma réponse (Fil #3), cela n'a plus aucune utilité d'utiliser la fonction DECALER pour rendre une plage dynamique.

1. convertis la plage de cellules qui te sert de référence pour ta liste en tableau structuré
2. nommer la plage de cellules contenant les données donc sans le titres et la ligne des totaux si elle est activée (gestionnaire des noms)
3. référencer ce nom dans la Validation de données-Liste

et tu ne dois plus te tracasser. Ce sera dynamique

A lire éventuellement : Apprendre à utiliser les tableaux structurés Excel : création, manipulations et avantages(1)

[RJSCVF]

Merci pour votre réponse !

J'ai essayé la méthode que vous m'aviez proposée avec le tableau structuré.
Mais voici ce qui se passe :


Comme vous pouvez le voir, la cellule A4 a été incrémentée automatiquement lorsque l'on a ajouté une date avec l'année 2020 dans une autre table créée à cet effet. Mais le tableau structuré ne s'étend pas ver le bas pour la considérer. C'est-à-dire que ma zone de liste déroulante se limite à A3.

Il est à noter que toutes les cellules de A1 à A21 contiennent une formule qui leur permettent de tirer l'année sans doublon à partir d'une autre feuille du même classeur (la table créée à cet effet).

Je ne sais pas qu'est-ce que vous pouvez me conseiller ?

Merci

[Philippe Tulliez]

Bonjour,
Effectivement, la création de la ligne ne peut être dynamique./
Une astuce peut-être mais il faut voir si la contraint est acceptée. Si l'on traite l'année la plus récente et les x précédentes, il y a une solution avec une formule
Dans l'illustration ci-dessous, nous avons à gauche le tableau d'encodage avec en colonne A les dates de traitements et à droite la liste déroulante. Les deux tableaux sont des tableaux structurés
Nous supposons que nous traitons l'année la plus récente encodée et les 5 précédentes
Donc en première ligne du second tableau (en brun), nous tapons la formule

Code :

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

=ANNEE(MAX(Tableau3[Date]))

dans la deuxième lignes et les quatre suivantes

Code :

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

=D2-1

Ainsi si l'on tape dans le premier tableau une date en 2020, automatiquement dans le second tableau, la première année sera 2020 et la dernière 2016
Si maintenant cette contrainte n'est pas bonne, il y a peut-être une solution avec Power Query mais je ne maîtrise pas encore ce produit pour apporter une réponse pertinente
Solution 2 : VBA
Solution 3 : Rester avec DECALER
Avez-vous essayé avec 4 points d'interrogations ? (Attention pour que cela fonctionne les valeurs doivent être alpha numériques)

Code :

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

=DECALER($A$2;;;NB.SI(D_AnnééCompta;"????*"))

Illustration

[RJSCVF]

Merci pour vos réponses Philippe Tulliez !
Je vais essayer la solution que vous avez illustrée. Si elle me convient, je l'adopterai. Sinon je crois que j'essaierai une solution en VBA.
Je vous tiens informé.
Merci