Ne pas faire évoluer la cellule d'une formule après un insert copier cellule

[mistercoke]

Bonjour, voici ma problèmatique :
1 / J'ai une feuille 1 qui est une base de donnée mise a jour via un téléchargement d'un fichier sur internet.
2 / Je copie les informations de la feuille 1 et je fais "Inserer copier cellule" dans ma feuille 2 pour ensuite y renseigner 3 catégories ( Qui sont des listes imbriquées )
3 / Dans ma feuille 3 je fais un récap en fonction de mes 3 catégories avec la fonction matricielle "sommeprod". Seulement à chaque insertion de "inserer copier cellule" cela me fait évoluer la cellule de ma formule d'où valeur erronée. Ex :=SOMMEPROD((A_Renseigner!$B2:$B65536=$A$1)*(A_Renseigner!$C2:$C65536=B1)*(A_Renseigner!$H2:$H65536="Recette");(A_Renseigner!$N2:$N65536))-->après l'insertion de 5 lignes par exemple la formule devient =SOMMEPROD((A_Renseigner!$B7:$B65536=$A$1)*(A_Renseigner!$C7:$C65536=B1)*(A_Renseigner!$H7:$H65536="Recette");(A_Renseigner!$N7:$N65536)).

Au final j'ai testé l'écriture suivante :
* avec $C2 elle évolue en $C7
* avec $C$2 elle évolue en $C$7
* en nomant la cellule c'est le même décalage

Quelqu'un aurait il une idée pour avoir tout le temps la même cellule ( Ici C2 ) quelque soit les insertions de 'Insérer copier cellule"? Sachant que le nombre de ligne à inserer dans mon tableau n'est pas fixe et peut aller de 1 ligne à je ne sais pas combien?.

 

[chris]

Re : Ne pas faire évoluer la cellule d'une formule après un insert copier cellule

Bonjour

Pas facile sans exemple.

1ère remarque, un sommeprod sur 65000 lignes , ça va ramer...

Si tu ne peux éviter les insertions il faut raisonner en dynamique et construire les plages avec la fonction DECALER ou bien utiliser INDIRECT

SOMMEPROD((DECALER(A_Renseigner!$B1,0,0,nbval(A_Renseigner!$B:$B)-1);1)=$A$1).....
s'il n'y a pas de trou dans le colonne B (sinon ajuster le nbval sur une colonne sans trou ou remplacer par 65000)

SOMMEPROD((INDIRECT("A_Renseigner!$B2:$B65536")=$A$1).....

On peut aussi dynamiser la partie basse de la plage référencée dans INDIRECT avec NBVAL

 

[mistercoke]

Re : Ne pas faire évoluer la cellule d'une formule après un insert copier cellule

Salut Chris, merci d'avoir pris le temps de me lire je vais faire un fichier excel en piece jointe pour t'expliquer mes besoins, cela sera très certainement plus simple comme tu peux le souligner.

 

[chris]

Re : Ne pas faire évoluer la cellule d'une formule après un insert copier cellule

Bonjour

En réponse à ton MP

INDIRECT("A_Renseigner!$B2:$"& nbval(colonne sans trou)-1)

exemple INDIRECT("A_Renseigner!$B2:$"& nbval($A:$A)-1)

 

[mistercoke]

Re : Ne pas faire évoluer la cellule d'une formule après un insert copier cellule

Hello, je voulais vous envoyez un fichier, mais avec winzip 12 pro il m'annonce fichier invalide?? Pourtant il fait que 26k. MAis je n'arrive pas a mettre en application la formule et me renvoi #ref.Youupii les amis j'ai réussi en fait lors de l'install de winzip j'avais choisi un mode compression nouvel génération qui fait des fichiers .zipx alors que le forum veut du fichier.zip mais la compression est moindre. de 26k je passe a 47k. mais ca passe

 

[jeanpierre]

Re : Ne pas faire évoluer la cellule d'une formule après un insert copier cellule

Bonjour mistercoke, chris (bises),

Tu le zippes avec le compresseur de Windows tout simplement.

Clic droit sur ton fichier et Envoyer vers/Dossier compressé.

Bon après-midi.

Jean-Pierre

 

[chris]

Re : Ne pas faire évoluer la cellule d'une formule après un insert copier cellule

Bonjour
Bise à Jean Pierre

Deux erreurs :
une de ma faute dans l'exemple donné (mea culpa)

INDIRECT("A_Renseigner!$B2:$"& nbval(colonne sans trou)-1)

n'est pas bon c'est

INDIRECT("A_Renseigner!$B2:$B"& nbval(colonne sans trou))

l'autre de la tienne car les colonnes ne correspondaient pas

 

[mistercoke]

Re : Ne pas faire évoluer la cellule d'une formule après un insert copier cellule

Salut Chris et Jean-Pierre
Merci pour la correction, je vais de ce pas faire les modifs sur mon fichier final voir si cela me permet d'avoir moins de temps de réponse ( Recalcul excel ) et je vous tiens au courant.
Mille merci la team pour votre support ;-)

 

[chris]

Re : Ne pas faire évoluer la cellule d'une formule après un insert copier cellule

Re

J'ai corrigé la correction dans mon précédent post (c'était bon dans l'exemple joint) : le -1 avec NBVAL s'utilise avec DECALER mais pas avec INDIRECT.