Microsoft 365 Pb de formules qui se modifient dans des cellules verrouillées

[Mickaël49]

Bonjour,
J'ai créé il y a plusieurs années un fichiers Excel qui nous permet de suivre la productivité par équipe. Ce fichier comporte des cellules à renseigner (Poste, opérateur, référence, quantité réalisée et heures de présence) qui sont déverrouillées et des cellules verrouillées qui se remplissent automatiquement via des formules que j'ai créées. C'est ces cellules qui nous donnent les infos de productivité. La feuille est protégée afin de ne pas effacer les différentes formules et j'ai juste autorisé la sélection cellules déverrouillées.
Ce fichier fonctionne nous donne entière satisfaction. Mais régulièrement, il y a des formules qui sont dans les cellules verrouillées qui se modifient.
Les modifications concernent la références à des cellules qui sont dans les formules. Soit la cellule de référence pour le calcul change soit elle est remplacée par "#REF!"
Nous avons d'abord cru qu'un utilisateur malveillant ôtait la protection de la feuille et modifiait les formules. Ensuite nous nous sommes rendu compte qu'un "couper" "coller" sur les cellules accessibles modifiait les formules des cellules verrouillées donc nous avons passé la consigne de ne pas en faire. Mais nous avions toujours des formules qui se modifiaient.
Récemment j'ai réussi à reproduire le problème à l'aide de la souris. je me suis rendu compte que si je cliquais (clic gauche) rapidement et de façon désordonner sur les cellules non verrouillées au bout d'un moment les formules qui étaient dans les cellules verrouillées se modifiaient.
Pouvez vous m'apporter votre aide ?
Quelle modification puis je faire pour ne plus avoir ces bugs ?
Merci d'avance
Mickaël

 

[Efgé]

Bonjour @Mickaël49 et bienvenu sur le forum

Sans exemple pour tester, difficile de répondre.
Pour éviter les #REF lors de la suppression de cellule, il est préférable d'utiliser INDIRECT qui ne sera pas sensible à la disparition des cellules.
Par exemple, remplacer = FEUILLE1!A1 par =INDIRECT("Feuil1!A1").

Après, il est vrai que certains utilisateurs développent des trésors d'ingéniosités pour trouver des possibilités imprévisibles....

Bon courage
Cordialement

 

[Mickaël49]

En pièce jointe le fichier concerné. j'ai supprimé des feuilles d'archivage et suivi pour alléger le fichier.

 

[Mickaël49]

Exemple d'une formule qui s'est modifiée pour le Pb de "#REF!" la cellule "H10" existe toujours dans la feuille de calcul.
Formule d'origine
=SI(H10="";"";SI(NB.SI(Opérateurs;H10);"";"NOM INCONNU"))
Formule modifiée
=SI(#REF!="";"";SI(NB.SI(Opérateurs;#REF!);"";"NOM INCONNU"))

 

[Mickaël49]

Je n'arrive pas à reproduire le problème de changement de cellule...

 

[Efgé]

Re
Je pense que la cellule H10 a été supprimée.
Bien sûr la cellule H10 existe toujours (on ne peux pas faire de trou sur une feuille....)
C'est au moment de la suppression que la formule ne retrouve plus ses marques.

Je ne pense pas pouvoir aller plus loin.
Cordialement

 

[Mickaël49]

J'ai réussi à reproduire le Pb de changement de cellule voir photo jointe.
Le G47 c'est transformé en G48 ??

 

[Mickaël49]

Re
Je pense que la cellule H10 a été supprimée.
Bien sûr la cellule H10 existe toujours (on ne peux pas faire de trou sur une feuille....)
C'est au moment de la suppression que la formule ne retrouve plus ses marques.

Je ne pense pas pouvoir aller plus loin.
Cordialement

Ok mais quel manipulation peut supprimer cette cellule sachant que j'utilise uniquement le clic gauche de la souris ??

 

[Mickaël49]

Ce qui peut donc se passer c'est que la suppression d'une cellule crée le "#REF!" et que l'ajout décalle vers le cellule en dessous dans la formule.
Mais quelle manip fait cette suppression/ajout de cellule ?

 

[Mickaël49]

Bonjour,
Avec un "couper" "coller" sur une cellule qui est dans une formule j'ai le même problème.
J'ai essayé de bloquer les cellules dans mes formules (F4) mais ça ne change rien.
existe t'il un moyen de bloquer définitivement toutes les formules d'une feuille ?
Je précise que toutes les cellules qui comportent un calcul sont verrouillées mais ça ne change rien..

 

[Efgé]

Bonjour

Pour figer ce genre de problème il faut remplacer les A1 par INDIRECT("Feuil1!A1")..
Cela oblige à modifier toutes les formules.

Cordialement

 

[Mickaël49]

Merci Efgé
Je viens d'essayer et effectivement la formule ne se modifie plus. Par contre la modification de toutes les formules va prendre un certain temps...
Est qu'il existe une solution qui autoriserait uniquement la saisie de valeur dans des cellules et qui interdirait tout le reste (les couper, les modification de format ...)?

 

[Efgé]

Re
Il faut passer par des macros.
Ca devient vite ingérable à cause de toutes les possibilités à prendre en compte.

Cordialement

 

[TooFatBoy]

Bonjour,

Récemment j'ai réussi à reproduire le problème à l'aide de la souris. je me suis rendu compte que si je cliquais (clic gauche) rapidement et de façon désordonner sur les cellules non verrouillées au bout d'un moment les formules qui étaient dans les cellules verrouillées se modifiaient.

Ce qui peut donc se passer c'est que la suppression d'une cellule crée le "#REF!" et que l'ajout décalle vers le cellule en dessous dans la formule.
Mais quelle manip fait cette suppression/ajout de cellule ?

Si tu cliques tous azimuts, peut-être as-tu, sans t'en rendre compte, déplacé une cellule ?

Par exemple, tu as peut-être déplacé la cellule D11 sur la cellule D12, et du coup en cellule D13 tu n'aurais plus

=SI(D8="";"";D11/D12)

mais

=SI(D8="";"";D12/#REF!)

.

 

[TooFatBoy]

Ensuite nous nous sommes rendu compte qu'un "couper" "coller" sur les cellules accessibles modifiait les formules des cellules verrouillées donc nous avons passé la consigne de ne pas en faire. Mais nous avions toujours des formules qui se modifiaient.

C'est pour ça qu'il est conseillé de ne jamais utiliser "couper/coller", ni même "copier/coller" qui met trop le bazar, en particulier au niveau des MFC : voir les MFC de ta feuille "Suivi par équipe".

Plutôt utiliser "copier" puis "collage spécial", pour coller par exemple la valeur ou la fonction.