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

Nylream

XLDnaute Junior
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 :
Code:
=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)
Code:
DECALER(C:C;0;-3)
me donne le contenu de la cellule mais
Code:
=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.
 

Fichiers joints

Dernière édition:

eriiiic

XLDnaute Barbatruc
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 :
VB:
Const feuilles As String = "RLS_Dispatch,RLNS_Dispatch,FMF_Dispatch,AIR_GROUP_Dispatch,RSA_Dispatch"
eric
 

Fichiers joints

Dugenou

XLDnaute Barbatruc
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

XLDnaute Junior
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.
Code:
=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.
Code:
=affectation(DECALER(D:D;0;-3);1)
Si jamais tu as une idée.

Merci d'avance,
 

Dugenou

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

Nylream

XLDnaute Junior
Bonjour Dugenou,

Merci pour le conseil,
Code:
=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 :(
 

eriiiic

XLDnaute Barbatruc
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

XLDnaute Junior
C'est bon, j'ai compris !
Merci beaucoup pour votre patience :p

J'ai crée un tableau sur D1: D250, et j'ai juste mis la formule suivante en D2:
Code:
=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
 

Fichiers joints

Nylream

XLDnaute Junior
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

XLDnaute Barbatruc
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
 

Fichiers joints

Nylream

XLDnaute Junior
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

XLDnaute Barbatruc
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

XLDnaute Junior
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

XLDnaute Barbatruc
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
 

Fichiers joints

Nylream

XLDnaute Junior
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
 

Nylream

XLDnaute Junior
Re,

Super pour les formules "simplifié", ça marche bien et ça m'évite d'avoir des pavé de formule à modifier
Merci beaucoup :)

Si je ne dis pas de bêtises, la liste personnalisé ne me permet pas d'automatiser ce tri, et je devrais toujours aller sur Tri, et le lancer manuellement.

N'y a t'il pas un moyen de faire que ce tri se fasse dès l'instant ou une des valeurs concerné change ?

Merci encore !
 

Dugenou

XLDnaute Barbatruc
Bonjour,
A mon avis ce n'est possible que par macro, ou dans un TCD mais dans ce cas vous devrez toujours faire une actualisation.
Cordialement
 

Fichiers joints

Nylream

XLDnaute Junior
Bonjour Dugenou,

Merci pour ta réponse,
Je ne vois pas trop comment un TCD pourrait faire ça, j'ai vue que tu avais mis une feuille 2, mais je ne suis pas sur de comprendre l'idée.

Si on pouvait n'avoir qu'un bouton, ça m'irait bien, mais je n'ai pas trouvé de moyen de faire une sorte de raccourci qui lance mon tri.

Par contre, j'ai l'impression que ma recherche intuitive s'est cassé... (voir les 2 PJ) elle gêne la saisie au lieu de l'assister. Je me sens un peu comme sur un manège la... ça me donne le vertige :(

Merci pour ton aide,
 

Fichiers joints

Dugenou

XLDnaute Barbatruc
La feuille 2 ne m'a servi qu'à enregistrer la liste personnalisée.
Je ne comprends ce que tu entends par recherche intuitive.
Oublies le TCD ce n'est pas une bonne idée
Pour le tri par un bouton : tu le fais une fois avec l'enregistreur de macro puis tu crées un bouton auquel tu affectes la macro.
Cordialement
 

Nylream

XLDnaute Junior
Re,
Petite question sur le tri personnalisé. Est ce lié a l'application ou ou fichier .xlsm ?
J'essaie depuis l'ordi du boulot et mon tri personnalisé n'est pas présent.
 

Discussions similaires


Haut Bas