XL 2010 Intégrer Adresse de cellule dans une formule, sans coordonnées.

[Nylream]

Bonjour à tous,

Dans mon tableau (ci joint) les colonnes assignment et role contiennent des formules permettant de récupérer les valeurs venant d'autre feuilles. Ça fonctionne bien, mais j'aimerais améliorer 2 choses si c'est faisable.

Les formules sont composé à peu près de la sorte :

=INDEX(RLS_ROLE;SOMMEPROD((RLS_NAME=A2)*LIGNE(RLS_NAME))-1)

Avec un SIERREUR pour enchainer les recherches sur les multiples feuilles.

Ce que j'aimerais avant tout ce serait de créer un champ "Assignment" et un champ "Role", et que la formule ne soit plus dans chaque cellule, mais affecter à tout le champ.
Cela permettrait par exemple d'automatiquement avoir la formule, même si une ligne est insérer.
A ce jour, si on insère une ligne, les formules ne se mette pas automatiquement dessus.

Pour ce faire, j'ai chercher à remplacer la matrice indiquant la cellule, (ci dessus en A2)

DECALER(C:C;0;-3)

me donne le contenu de la cellule mais

=INDEX(RLS_ROLE;SOMMEPROD((RLS_NAME=DECALER(D:D;0;-3))*LIGNE(RLS_NAME))-1)

ne fonctionne pas et me renvoie un #N/A
Je ne vois pas trop comment contourner mon problème, et j'ai du stopper mes essais téméraires, Excel commençait à se goinfrer à plus de 2Go de ram...
_

Autre demande sur le même tableau
J'aimerais faire un tri automatique du tableau, de A2 à K250, mais personnalisé sur la colonne G, et avec les critères suivants:
O-11 à O-1,O-C, E-9 à E-1, W-5 à W-1.

J'ai cru comprendre que certaine macro VBA permettait un tri, mais je n'ai pas trouvé de version ou celui ci était personnalisable.

Je précise que je suis un complet débutant sur Excel, la quasi totalité des macro, ou formules m'ont été communiqué ici (merci Mapomme) ou prise sur le net (site de boisgontierjacques).

Merci d'avance pour votre aide,

Nyl.

 

[eriiic]

Bonjour,

Un effort d'explication mais ça reste nébuleux. 20 lectures, 0 réponses, je ne dois pas être le seul à être dans l'expectative.

Ce que j'aimerais avant tout ce serait de créer un champ "Assignment" et un champ "Role", et que la formule ne soit plus dans chaque cellule, mais affecter à tout le champ.

rien compris...

Cela permettrait par exemple d'automatiquement avoir la formule, même si une ligne est insérer.
A ce jour, si on insère une ligne, les formules ne se mette pas automatiquement dessus.

Si tu transformes ta plage en Tableau (menu Insérer) ça se fait tout seul.

Des fois que ça te serve j'ai créé une fonction personnalisée pour remplacer ta formule à rallonge.
=affectation(A2;1) retourne Assignement
=affectation(A2;2) retourne Rôle
Compléter ou adapter si besoin la constante :

Const feuilles As String = "RLS_Dispatch,RLNS_Dispatch,FMF_Dispatch,AIR_GROUP_Dispatch,RSA_Dispatch"

eric

 

[Dugenou]

Bonjour,
Je n'ai pas trop le temps d'inspecter ton fichier, mais essayes déjà de limiter le décaler à une plage de 1000 lignes plutôt qu'une colonne entière : le sommeprod sur plus d'un million de lignes n'est pas idéal.
Cordialement

 

[Nylream]

Bonjour Eric et merci pour ta réponse.

En effet, J'ai essayé d'être précis mais j'ai l'impression que trop d'explication a rendu mon post finalement rébarbatif, désolé.

Merci beaucoup pour la fonction personnalisé.
Je savais même pas que c'était possible. J'ai vue dans module 2 la ligne "Const feuilles As String = "**", si je veux rajouter des feuilles *_Dispatch" par la suite, c'est ici que je dois le faire je suppose.
Tout le module 2 est nécessaire ou juste cette ligne pour que ça fonctionne ?

Sinon pour mon problème, ça apparaitra plus clair dans ta formule mais ma demande reste la même
Pour chaque ligne, la formule change:
=affectation(A2;1)
=affectation(A3;1)
=affectation(A4;1)
etc...

J'aurais voulu que la formule reste la même pour l'affecter automatiquement à la colonne, de façon à ce que même si on insère une ligne, la formule soit présente.

=DECALER(D:D;0;-3)

me donne bien le contenu de la ligne sur la colonne A.
Mais si j'essaie de le formuler comme suis, ça ne marche pas.

=affectation(DECALER(D:D;0;-3);1)

Si jamais tu as une idée.

Merci d'avance,

 

[Dugenou]

Re,
Pour que la formule s'ajoute quand tu insères une ligne : travailler dans un "tableau" (insertion/tableau)
Cordialement

 

[Nylream]

Bonjour Dugenou,

Merci pour le conseil,

=affectation(DECALER(D2:D250;0;-3);1)

Donc comme ci dessus pour éviter l'utilisation de ressource immodéré

Mais ça ne marche pas mieux

 

[eriiic]

Tout le module 2 est nécessaire ou juste cette ligne pour que ça fonctionne ?

??? Tout à fait, j'ai mis le reste juste pour décorer et me la péter ;-)

Je crois avoir compris ce que tu veux. C'est une fonction matricielle que tu validerais sur une plage.
Au-delà du fait que c'est un peu difficile pour l'utilisateur de modifier une telle formule (il faut retrouver la plage de la matrice complète), ça ne changera pas ton problème en cas d'ajout de ligne en bas.
Ta plage ne s'étendra qu'en cas d'insertion et c'est tout.
Inconvénient que ne présente pas un tableau qui est une avancée majeure, je ne comprend pas que tu n'essaies pas.
Sinon on peut faire une maj complète par un bouton, sans formule. Mais il faudra une action manuelle.
Ou bien remplir C: D sur l'événement Change en A, mais regarde les tableaux d'abord.

 

[Nylream]

C'est bon, j'ai compris !
Merci beaucoup pour votre patience

J'ai crée un tableau sur D1: D250, et j'ai juste mis la formule suivante en D2:

=SIERREUR(affectation(A65;1);"Unknow")

Par contre, si le champ NAME correspondant est vide, ça me met par defaut la première correspondance avec un champ vide (SHIP2). Comme montré si dessus, j'ai essayé avec SIERREUR, mais vide n'étant pas une erreur, ça n'a pas mis "Unknow"

EDIT: ajout de la pièce jointe

 

[Nylream]

Désolé Eric,
Pour le module, je ne savais pas si tu avais insérer du code dans une partie existante ou si c'était unmodulen entier à part entière, je suis un peu lent pour ce genre de chose, mais j'ai compris

Pour le tableau, je n'avais pas compris comment ça fonctionnait, là encore, ne connaissant pas bien les termes, il m'a fallu un moment pour comprendre

Merci pour ton aide,

 

[Dugenou]

Pas bien compris : la fonction semble ne pas être présente, pas de tableau dans cette pj.
J'ai créé le tableau, j'ai nommé la formule complète avec somme prod (juste pour faciliter la lecture. Voir colonne assignment. La formule me semble correcte
voir pj
Cordialement

 

[Nylream]

C'est génial, merci beaucoup Dugenou,
Il faut que je vois si j'arrive à reproduire ça pour ma colonne "Role".
_

Sinon, au cas ou, aurais tu une piste pour automatiser le tri personnalisé ?
Est-ce possible sans VBA ?

Merci encore pour votre aide à tout les deux,

 

[Dugenou]

Re,
Se placer en E2, copier la formule après avoir remplacé tous les A2 par des $A2.
Formules, gestionnaire de nom/nouveau : choisir un nom (sans chiffres et sans espaces) et coller la formule en bas dans "fait référence à ", valider.

Pour le tri personnalisé : quel est l'objectif ? sur quelle colonne ?
Cordialement

 

[Nylream]

Re,
Je ne pourrais pas tester du boulot mais je le ferais dès que je le pourrais ce soir.

Concernant mon tri, j'aimerais trier le tableau basé sur ce que contient la colonne G, suivant un ordre précis.
Du haut vers le bas:
O-11, O-10, O-9, O-8, O-7, O-6, O-5, O-4, O-3, O-2, O-1, O-C
W-5, W-4, W-3, W-2, W-1
E-9, E-8, E-7, E-6, E-5, E-4, E-3, E-2, E-1

Voilà, si tu as une idée...

En te remerciant pour ton aide,

 

[Dugenou]

Re,
Il n'y a rien dans la colonne O du dernier fichier

Il suffit d'enregistrer ta liste dans l'ordre que tu veux : fichier/options avancées, en bas dans général : bouton Modifier les listes personnalisées, cliquer sur importer (tu peux aussi sélectionner une autre plage) (voir feuille2)
ensuite dans ton tableau : données /trier/ordre : choisir personnalisé et là tu trouves la liste que tu as créée. Attention il y a une limite à 255 caractères (

Voir pj

 

[Nylream]

pardon, ma liste de O-11 à E-1 c'est mon critère de classement, ça ne correspond pas à des cellule, mais c'est le texte des cellules dans la colonne G
Désolé pour la confusion