#REF! après suppression de lignes

[Franckhy]

Bonjour à tous

Je précise en préambule que j'ai un niveau qu'on pourrait qualifier de "néophyte-plus" sur OpenOffice, connaissance des fonctions "basiques", quelques créations de tableaux à mon actif mais uniquement avec des fonctions simples (pas de macros etc...) et plusieurs tentatives finalement abandonnées face à la "complexité" de la chose de mettre en route les bases de données.

Exposé de mon problème :

J'utilise, dans le cadre de mon travail, des tableaux qui ont une double fonctionnalité : gestion au sein de notre association et suivi d'information auprès des bénéficiaires de nos prestations.
Pour chacun de nos "clients", est créé un nouveau document calc comprenant différentes feuilles, par duplication d'un fichier "modèle" standardisé.
L'une de ces feuilles se présente sous la forme d'un genre de calendrier dressant l'historique des prestations effectuées.

CaptureOpenCalcExemple.PNG

Comme indiqué plus haut, chaque fichier-client est généré à partir de notre modèle. Ce modèle a été calibré sur une version "moyenne" de consommation mais nécessite souvent des adaptations, et c'est là que surgit le problème :
La "moyenne" ayant servi de référence est basée sur 4 lignes (4 journées d'intervention) pour chaque mois de l'année, mais il arrive souvent qu'il faille ajouter ou supprimer des lignes, et alors surgissent les problèmes de < #REF ! >

CaptureOpenCalcExempSupp.PNG

J'ai parcouru le forum et trouvé divers sujets y faisant allusion, mais aucune réponse à mon problème précis de "mise à jour" des formules dans des cas d'ajouts ou suppression de lignes sur des structures telles que nous utilisons.

Ma question est donc : existe-t-il une "formule magique" ou une méthode permettant d'insérer ou de supprimer des lignes dans une feuille et que les références soient "actualisées" en fonction de cette modification ?

Pour faciliter la résolution, ci-dessous la "grille" de notre tableau avec les formules de calculs utilisées afin de mieux comprendre l'utilisation faite des ref :

CaptureOpenCalc1.PNG

NOTE: Comme vous aurez pu le constater il y a un formatage conditionnel actif sur 2 cellules (les colonnes J et K de chaque ligne de "total mensuel"), je n'ai par contre jamais réussi (même en suivant les explications trouvées ici https://forum.openoffice.org/fr/forum/v ... out+lignes) à gérer la mise en forme de chaque ligne selon notre mise en page, mais c'est un autre sujet...

Merci d'avoir lu ma question, et de m'apporter vos éclairages.

[micmac]

Bonjour et bienvenue sur le forum,

OpenOffice v.4.1.5 sous Windows 8.1

Votre signature fait mention d'une version de OpenOffice qui n'est plus maintenue.
Est-ce une erreur, un oubli, un choix, une obligation ?
Dans ce dernier cas, mentionnez-le dans votre signature par Obligation de version.

Afin de profiter des dernières améliorations, il est fortement recommandé d'être à jour de la dernière version 4.1.7

Accès direct à votre signature pour la corriger.


Le titre de votre question n'est pas assez explicite ou ne reflète pas le problème que vous décrivez : < Problème de #REF! > Nous l'avons donc modifié. En effet, lors d'une recherche, ce sujet risquait de passer inaperçu et donc de ne pas aider d'autres personnes ayant le même problème que vous.
Des termes implicites comme macro, aide, problème ou OpenOffice n'ont pas besoin d'être mentionnés.

Cordialement.

[jeanmimi]

Bonjour,

t alors surgissent les problèmes de < #REF ! >

Cette FAQ (erreur 524) donne la raison :
https://wiki.openoffice.org/wiki/FR/FAQ/Calc/015
Dans ton cas, il faudrait plutôt joindre le fichier qu'une copie d'écran pour tester les formules.

[Franckhy]

OpenOffice v.4.1.5 sous Windows 8.1

Votre signature fait mention d'une version de OpenOffice qui n'est plus maintenue.
[...] Le titre de votre question n'est pas assez explicite ou ne reflète pas le problème que vous décrivez

Bonjour MicMac, et merci pour la réponse et la correction.

Concernant la version, c'est un "oubli"... je croyais pourtant avoir la dernière, il me semblait l'avoir téléchargée il y a peu, je vais donc mettre à jour dans la foulée.

Concernant l'intitulé, mille pardon, j'avais pourtant bien lu les instructions préalables à l'envoi d'une question....


EDIT : Bon, ben pas de mise à jour pour tout de suite.... (???)

Capture.PNG

[Franckhy]

Bonjour,

t alors surgissent les problèmes de < #REF ! >

Cette FAQ (erreur 524) donne la raison :
https://wiki.openoffice.org/wiki/FR/FAQ/Calc/015
Dans ton cas, il faudrait plutôt joindre le fichier qu'une copie d'écran pour tester les formules.

Bonjour Jeanmimi.
La raison de l'apparition de l'erreur, je l'avais bien identifiée... mais jusqu'à présent je n'ai pas réussi à trouver une parade "automatique" et suis donc contraint d'effectuer les correctifs manuellement sur chaque fichier. C'est cette tâche laborieuse que j'aimerais parvenir à m'épargner en trouvant "la bonne astuce" (ou commande) pour que les formules s'auto-corrigent lorsqu'est ajoutée ou supprimée une ligne dans un mois...

Ci-joint le fichier :

XX_fiche suivi exemple.ods

[jeanmimi]

Dans le fichier que tu as joint, j'i ajouté une ligne,mais je ne vois pas d'erreur REF, sans doute parce qu'il n'y a pas de formule dans la colonne Différentiel.

[Franckhy]

alors en effet, je réalise que j'avais (très) mal formulé...

En effet, lorsqu'on ajoute des lignes, le problème qui survient n'est pas le #REF, mais la non prise en compte des données de la ligne ainsi ajoutée sans une correction de celles qui la suivent.

Pour s'en rendre compte, il suffit de documenter les plages horaires (format HH:MM) : il ne se passe rien sur la ligne ajoutée, les totaux ne sont pas impactés non plus, contrairement à ce qui se passe lorsqu'on documente une ligne pré-existante.

voir ici, la ligne surlignée en bleu ajoutée et renseignée, mais non prise en compte dans le calcul final

CaptureOpenCalcExemple.PNG

CaptureOOC-Ajout.PNG

Et à contrario, lorsqu'on supprime une ligne déjà existante, les suivantes affichent un #REF, car cette suppression n'influe pas sur les références utilsées dans les formules.
Concernant la colonne "différentiel" le calcul n'est, en effet réalisé que sur la ligne "total du mois"

[Michel JANZEF]

Bonjour,
La mise à jour automatique d'AOo n'existe pas ! Il faut vous rendre sur le site officiel pour télécharger la nouvelle version.
Même si vous êtes déjà utilisateur d'AOo, je vous recommande par ailleurs de suivre le tutoriel d'installation.

[jeanmimi]

Comme les formules n'ont pas été recopiées, c'est normal que les totaux ne changent pas.

[Franckhy]

Bonjour,
La mise à jour automatique d'AOo n'existe pas ! Il faut vous rendre sur le site officiel pour télécharger la nouvelle version.
Même si vous êtes déjà utilisateur d'AOo, je vous recommande par ailleurs de suivre [url=https://forum.open%20office.org/fr/forum/viewtopic.php?f=39&t=49080]le tutoriel d'installation[/url].

J'ai constaté en effet, et c'est ce que j'ai fait. Merci.
L'installation est actuellement en cours, je mettrai la signature en conformité une fois l'installation terminée et opérationnelle.
Par contre je crains fort de me retrouver, du fait de cette procédure, avec deux versions qui cohabitent sur mon PC puisqu'il y a eu installation de la 4.1.7 et non mise à jour de la 4.1.5 existante en 4.1.7...

[EDIT : Mise à jour OpenOffice téléchargée et installée : ok. Version 4.1.5 supprimée, signature actualisée. Tout baigne

[Franckhy]

Comme les formules n'ont pas été recopiées, c'est normal que les totaux ne changent pas.

Effectivement. Là encore j'opère une correction manuelle à chaque insertion. C'est un pu fastidieux, mais reste simple et sans gros risque de fausser les résultats de la feuille attendu que les ajouts lorsqu'ils se produisent ne se font qu'une ligne à la fois. J'aimerais assez, cependant, parvenir à trouver là aussi un correctif permettant d'automatiser cette manœuvre, sans succès jusqu'à maintenant... D'autant que j'aimerais également (tant qu'à faire) pouvoir automatiser la répercussion des styles appliqués, et là aussi je butte du fait des mises en forme différentes pour presque chaque case sur la ligne du tableau, certaines comportant de surcroit des styles dynamiques conditionnels...

Mais un seul problème à la fois, et pour rester en phase avec le titre du sujet ouvert, c'est surtout le cas "suppression de ligne(s) et survenue du #REF! que j'aimerais solutionner ici attendu que cette opération s'avère bien plus fréquente et porte souvent (en fin de mois) sur des suppressions multiples de ligne, situation favorisant la commission d'une erreur dans les références lors de la correction et ayant des répercussions sur toute la suite du document.

[Michel JANZEF]

[EDIT : Mise à jour OpenOffice téléchargée et installée : ok. Version 4.1.5 supprimée, signature actualisée. Tout baigne

Avez-vous bien installé, comme précisé dans le tutoriel, en faisant un clic-droit sur le programme puis en choisissant " Exécuter en tant qu'administrateur "? Sinon, vous risquez de revenir un de ces jours sur le forum.

[Franckhy]

Avez-vous bien installé, comme précisé dans le tutoriel, en faisant un clic-droit sur le programme puis en choisissant " Exécuter en tant qu'administrateur "? Sinon, vous risquez de revenir un de ces jours sur le forum.

Oui, merci.
De toute manière, je compte bien revenir sur le forum... mais pas toujours pour jouer au pénible avec des questions c*ns !

[DLE]

Bonjour,
Ci-dessous une proposition de solution à votre problème.
Pour résoudre le problème "#Ref!" en cas de suppression de ligne:
En "H19" l'instruction

Code : Tout sélectionner

=INDIRECT(ADRESSE(LIGNE()-1;COLONNE()))+G19

prend la valeur de la cellule supérieure et l'additionne à G19, cette formule doit être recopiée dans les cellules en-dessous avec la poignée en bas à droite de la cellule "H19". Voir l'aide en ligne pour les instructions "Indirect" et "Adresse".

Pour insérer une nouvelle ligne :

• sélectionner une ligne, par exemple la ligne 21
  Ctrl + C
  Sélectionner la ligne 22
  Menu "Edition">"Collage Spécial", cocher les cases "Formule" et "Déplacer les cellules Vers le bas" clic sur "OK"
  La ligne sera insérée avant la ligne 22

En espérant avoir été clair dans mes explications.
Cdlt.

[jeanmimi]

c'est surtout le cas "suppression de ligne(s) et survenue du #REF! que j'aimerais solutionner

Je reproduis si je supprime la ligne u est juste en dessous de la dernière ligne remplie.
Par contre, je ne reproduis pas ie le total du mois est la somme de la colonne

Code : Tout sélectionner

=SOMME(H19:H22)

et non pas la quantité de la dernière cellule, par exemple :

Code : Tout sélectionner

=H22

Je ne reproduis pas non plus si je supprime des lignes, en commençant par la ligne juste au-dessus du total.

[Franckhy]

Merci DLE.

Je vais faire le test pour la suppression après avoir un peu étudié cette "ligne de commande" qui ne me parle pas trop comme ça en première lecture, histoire de comprendre son fonctionnement et sa syntaxe.

L'ajout étant bien plus simple à essayer j'ai testé, et ça marche... presque.
Pourquoi presque ? Parce que, en effet, ça m'a bien repris les formules des différentes cases, ça a bien décalé la suite vers le bas, mais... il y a un mais.
La formule de la ligne suivante n'a pas été mise à jour !
J'ai copié la ligne 29 de ma fiche, puis "créé" une nouvelle ligne 30 en poussant l'ancienne ligne 30 vers le bas, qui est donc devenue la ligne 31
sauf que dans le calcul de la colonne H ("Cumul" sur ma feuille), la formule ne s'est qu'à demi mise à jour.
En effet, avant l'opération elle était à < =H29+G30 > qui aurait du se transformer, après "déménagement" en < =H30+G31 >
or ce que je trouve à l'intérieur, c'est : < =H29+G31 >. Si la ref de G a bien suivi, celle de H est restée la même...
Bizarre.
Merci néanmoins pour l'aide à trouver, et je fais un retour plus tard dans la soirée quand j'aurais étudié et testé la "formule de suppression".

[Franckhy]

Je reproduis si je supprime la ligne u est juste en dessous de la dernière ligne remplie.
Par contre, je ne reproduis pas ie le total du mois est la somme de la colonne

Code : Tout sélectionner

=SOMME(H19:H22)

et non pas la quantité de la dernière cellule, par exemple :

Code : Tout sélectionner

=H22

Désolé, je n'ai pas bien compris...
et dans la partie que je comprends (mais peut-être mal) la solution ne convient pas, car sur la ligne des totaux se trouve repris le dernier cumul des lignes de suivi, d'où le =H22 dans cet exemple, et si je le remplace par

Code : Tout sélectionner

=SOMME(H19:H22)

le résultat sera faussé puisque j'ajouterai H22 au total, alors que c'est lui le total (je ne sais pas si je suis clair)...
à la limite il faudrait que je teste avec

Code : Tout sélectionner

=SOMME(H19:H21)

peut-être...

EDITtest effectué avec

Code : Tout sélectionner

=SOMME(H19:H21)

et le résultat est totalement erroné...

[Franckhy]

Bonjour,
Ci-dessous une proposition de solution à votre problème.

Les tests du fichier fourni sont concluants en effet ! Mille mercis.
Ne me reste plus maintenant qu'à comprendre "pourquoi"
enfin à étudier la syntaxe afin de pouvoir reproduire aux divers endroits où je pourrais en avoir besoin..
Excellente piste en tout cas, mille fois merci !

[Franckhy]

Par contre, je ne reproduis pas ie le total du mois est la somme de la colonne

Code : Tout sélectionner

=SOMME(H19:H22)

et non pas la quantité de la dernière cellule, par exemple :

Code : Tout sélectionner

=H22

En testant le fichier, de mon côté j'ai toujours un #REF dans la colonne K lorsque je supprime la dernière ligne...

Et je confirme, la solution avec la fonction SOMME ne convient pas du tout, attendu que la colonne concernée (H) est le cumul des heures quotidiennes, donc on y trouve (avec J pour Jour et non comme indicateur de colonne) H1 = J1 ; H2 = H1+J2 ; H3 = H2+J3 etc... soit une incrémentation.
Pour illustrer avec des chiffres, supposons que chaque jour additionné soit de 8h, on obtient : H1=8 ; H2=16 ; H3=24 et H4=32 et donc Total=32
Si l'on utilise SOMME, on additionne toutes les incrémentations or c'est le résultat de la dernière incrémentation qui est nécessaire. Pour reprendre les chiffres ci-dessus avec SOMME on va obtenir 80 (soit 8+16+24+32) au lieu de 32 qui est la "bonne" valeur de cumul.

Par contre en écrivant ça, je viens de réaliser que la fonction SOMME peut très bien être utilisée, mais en prenant les chiffres de la colonne d'à-côté (col G) puisque c'est son contenu qui est utilisé pour l'incrémentation

[DLE]

En testant le fichier, de mon côté j'ai toujours un #REF dans la colonne K lorsque je supprime la dernière ligne...

Quelques préparations pour palier au problème

• Affectation d'un nom aux cellules "K17", "K26", "K33" de la forme par exemple "Tjanvier", "Tfevrier", "Tmars" ....
  En "K20" mettre la formule "

  Code : Tout sélectionner

  =Tjanvier-H20

  " et en "K29" "

  Code : Tout sélectionner

  =Tfevrier-H29

  ... pour chaque début de mois
  En "K21" mettre la formule "

  Code : Tout sélectionner

  =INDIRECT(ADRESSE(LIGNE()-1;COLONNE()))-H21

  " en "K30" "

  Code : Tout sélectionner

  =INDIRECT(ADRESSE(LIGNE()-1;COLONNE()))-H30

  ". Etendre ces formules dans les lignes du tableau mensuel, et faire cela pour chaque mois.

Dans le fichier joint je me suis arrêté au mois de mars.

A+ et bonne soirée.

[Franckhy]

Merci pour le temps et l"énergie consacrés.

Je dois partir sans tarder, j'ai chargé le fichier et vais regarder tout ça tout à l'heure quand je serai arrivé à destination.

Mais vraiment merci à tous, quelle réactivité, et quel soutient, ça fait chaud au cœur !
J'espère être en mesure d'en faire autant, certainement pas pour vous vu nos "échelles" de compétences".

[jeanmimi]

et le résultat est totalement erroné...

Oui, c'est pourquoi il faut faire le total

Code : Tout sélectionner

=SOMME(G19:G22)

afin d'éviter le #REF! en cas de suppression de ligne.