ExcelInsertion de ligne sans MAJ des formules Fermé

Question

Bonjour,

J'ai créé un document excel avec plusieurs onglets. Le premier est une liste qui synthétise les informations inscrites sur la centaine d'onglets suivante (une ligne dans la synthèse par onglet).

La liste du premier onglet est uniquement constituée de références à des cellules dans les onglets suivants de type ='Feuille2'!A1.

Le problème est que les fiches que j'ai créé sur chacun des onglets suivants sont amenées à être mises à jour, en insérant des cellules au dessus de ce qui a déjà été saisi.

Or, vous le savez comme moi, que l'on soit en référence relative ou absolue, excel met à jour les références lorsque l'on insère une ligne au dessus des informations liées, de manière à toujours y renvoyer.

Dans mon document, je souhaite que la référence soit strictement liée à un numéro de cellule, malgré l'insertion de ligne, et que par conséquent les informations auparavant liées ne le soient plus.

Auriez-vous une solution ?

J'espère que ma présentation du problème est suffisamment claire

Je vous remercie d'avance.

pijaku · Answer

Bonjour,
A moins de passer par une macro, sinon je ne vois pas...

Silverine · Answer

D'accord. Sauriez-vous comment rédiger une macro qui me permettrait de parvenir à mes fins ?

pijaku · Answer

En fait, cela me semble long et surtout difficile à faire à distance. Tu peux le faire, j'en suis sur!!!

Pour inscrire en cellule A1 de ta feuille Feuil1 la formule "=Feuil2!A1" par macro, cela ce note :
Sheets("Feuil1").Range("A1").FormulaR1C1 = "=Feuil2!RC"

Pour inscrire en cellule B23 de ta feuil1 la formule "=Feuil24!F15" par macro, cela ce note :
Sheets("Feuil1").Range("B23").FormulaR1C1 = "=Feuil24!R[-8]C[4]"

Tu peux placer ton fichier ici en pièce jointe ou me l'envoyer par mail privé... Ou encore utiliser l'enregistreur de macros... Outils/Macro/Nouvelle macro.

tompols · Answer

Bonjour,
Sans voir le fichier ç'est pas facile mais regarde du coté de la fonction DECALER()...

Silverine · Answer

Je vous propose un document excel semblable à l'original qui vous éclairera peut être un peu sur ce que je souhaite faire.

Vous pouvez le trouver sous le nom "Exemple" à l'adresse suivante : 

http://idisk.mac.com/eric.devismes-Public?view=web

Merci pour vos réponses

eriiic · Answer

Bonsoir,

remplace ='Feuille2'!A1
par
=indirect("'Feuille2'!A1")

mais plus le droit de renommer les onglets sans modifier ces formules, donne leur le nom définitif avant.

eric

Silverine · Answer

Merci beaucoup, vous avez parfaitement répondu à ma question.

Merci d'autant plus d'avoir précisé qu'il ne fallait pas modifier le nom des onglets lorsqu'on utilise cette fonction car dans le système que j'avais établi, ils étaient être renommés à chaque modification de fiche.

Bonne journée à tous

A bientôt

eriiic · Answer

Re,

Si tu veux pouvoir renommer tes feuille remplacer =Feuil3!B3 par :
=INDIRECT("'"&STXT(CELLULE("filename";Feuil3!A1);TROUVE("]";CELLULE("filename";Feuil3!A1))+1;32)&"'!B3")

Si tu dois être amené à supprimer des lignes ou des colonnes remplace les A1 par des valeurs plus élevées (Z100 par exemple, la référence de la cellule n'a pas d'importance mais doit toujours exister qcq soient les manip)
eric

Silverine · Answer

Merci pour ces précisions utiles. J'en profite pour poser une autre question : dans la colonne "Enseignes précédences" du tableau sur le premier onglet, je souhaite intégrer une formule qui indiquerait pour chaque établissement, les enseignes précédentes. Parfois, ce sont les mêmes, parfois, elles sont différentes, et parfois il n'y en a pas du tout. Le but de la manoeuvre serait d'indiquer, sans doublon, les enseignes précédentes séparée d'une virgules et d'un espace ainsi : Etablissement n-1, Etablissement n-2, etc. Le type de formule auquel j'avais pensé était : =SI(Enseigne n<>Enseigne n-1;Enseigne n-1;"")+SI(Enseigne n-1<>Enseigne n-2;" ,"+Enseigne n-2;"") etc. J'espère que ce début de formule vous aide à saisir l'esprit de ce que souhaite faire, sachant, comme dans ma première question, qu'il va falloir utiliser la fonction INDIRECT pour ne pas être troublé par les éventuelles insertions de lignes. Auriez-vous une solution ? Merci d'avance pour vos réponses.

eriiic · Answer

Ne sachant pas ce qu'est une enseigne précédente pour toi ni comment on la trouve impossible de te répondre.
Dépose un fichier exemple avec des explications sur cijoint.fr et colle ici le lien fourni.
eric

Silverine · Answer

Pardon, j'ai utilisé deux terminologies pour rien : enseigne et établissement désignent la même chose.

Le document que j'ai déjà posté devrait vous suffir pour saisir l'objet de ma question.

Merci

eriiic · Answer

Le document que j'ai déjà posté devrait vous suffir pour saisir l'objet de ma question
Si tu le dis...

Silverine · Answer

Bonjour,

J'ai posté un nouveau document plus clair pour ma question :

http://cjoint.com/data/isn0xpmits.htm

J'espère qu'il vous permettra de répondre à ma question.

Merci

eriiic · Answer

Bonjour,

Ca n'est pas faisable par formule.
On peut envisager une fonction personnalisée qui aurait ces parametres :
histoEtab(EtablissementActuel;histo1)
ce qui donnerait pour l'établissement A :
=histoEtab(A!D9;A!D15)

-on pourrait se passer du A! (qui désigne la feuille) si on peut retrouver le nom exact de la feuille concernée à partir du nom de l'établisssement de la feuille liste.
-D9 désigne la cellule du nom actuel de l'établissement (ça suivrait si tu insères des lignes sur la feuille A)
-D15 donne le décalage en lignes où aller chercher l'historique. Il faut qu'il soit constant, on s'arrete dès qu'une cellule est vide.

Dis moi ce que tu en penses...
Les macros ne supportent pas l'approximation, si tu veux que les feuilles soient trouvées automatiquement dis CLAIREMENT la règle que tu comptes appliquer pour retrouver le nom de l'onglet (en étant sûre qu'il n'y aura pas de doublons)

eric

Silverine · Answer

Bonjour,

Merci pour cette réponse, bien que j'avoue ne pas tout comprendre.

Je ne sais pas comment faire une fonction personnalisée. Cela passe-t-il par la création d'une macro ?

La solution que vous me proposez permet-elle d'éviter les doublons dans la colonne "Etablissements prcédents" de la liste ?

Concernant les onglets, ils sont tous nommés de manière unique, sans qu'il y ait de possibilité de doublon. Je les nomme de la manière suivante : 4-001, 4-002, 4-003,..., 4-107.

- Le nom de l'onglet et le nom de l'établissement figurent bien dans la feuille liste.

- Je précise que le nom actuel de l'établissement est en D3. 

- Ensuite, l'historique commence avec D9, puis D15, D21,... avec toujours le même décalage de 6 lignes. Il est en effet approprié que la liste s'arrête à partir du moment ou une cellule est vide, sachant qu'il est possible qu'elle soit remplie après une insertion de lignes.

Merci pour votre investissement à répondre à mes questions, je regrette de ne pas pouvoir vous proposer le document original.

Cdlt.

eriiic · Answer

Bonsoir,

Voici une proposition par fonction personnalisée (oui, c'est une macro en VBA).
Une fonction personnalisée, une fois mise dans le classeur, s'utilise comme une fonction normale d'excel, avec ses paramètres.
Tant que j'y étais j'ai fait en sorte que tu puisses ramener aussi les autres infos. Plus besoin des formules avec indirect().

J'ai supposé que la colonne A de ton fichier exemple était le num_Etablissement qui servait à fabriquer le nom de l'onglet concerné : 1 => 4-001

Syntaxe :
=infoEtab(num_Etablissement, info)
avec :
num_Etablissement : numéro de l'établissement
info : type d'info à ramener
    'info=0 : Historique Raison sociale
    'info=1 : Raison sociale en D3
    'info=2 : Adresse en D4
    'info=3 : Propriétaire en D5
    'info=4 : Exploitant en D6
    'info=5 : Naissance en D7
    'info=6 : Date de déclaration en B6
    'info=7 : Date précédente déclaration en B7

Exemple:
Tu veux la raison sociale de l'établissement dont le n° est en A3 :
=infoetab(A3;1)

Je ne développe pas plus pour l'instant sur comment l'intégrer sur ton classeur.
Teste déjà sur ce classeur pour voir si ça te va.
Tu peux aussi garder ce classeur ouvert et tester la fonction sur un autre classeur plus complet (mais c'est prévu pour ne marcher correctement que sur le classeur actif, ne soit pas surprise si tu as des résultats bizarres dans celui ayant la fonction)

eric

PS: je t'ai également mis une macro pour faire l'insertion des 6 lignes sur les feuilles nommées 4-xxx. 
Ctrl+i pour l'appeler
Les données précédentes sont recopiées pour ne modifier que celles ayant changé.

ExcelInsertion de ligne sans MAJ des formules

16 réponses

Discussions similaires