Comment conserver une formule fait référence à une feuille de calcul lorsque la feuille de calcul est supprimé et remplacé?

Je suis l'auteur d'une demande avant l'ouverture d'un classeur Excel, supprime et recrée certaines feuilles de calcul, comme par exemple "Sheet1" dans l'exemple ci-dessous.

D'autres feuilles de calcul (dire Feuil2) dans le même classeur peut avoir des formules qui font référence à l'remplacé la feuille de calcul, comme ceci:

=IF('Sheet1'!A9="","",'Sheet1'!A9)

Malheureusement, la référence dans la formule ci-dessus à la feuille Sheet1 est interrompue lorsque la feuille est remplacé, en devenant 

=IF(#Ref!A9="","",#REF!A9)

Quelqu'un peut penser à un moyen de code de cette formule afin de ne pas échouer? Il pourrait, par exemple, obtenir le nom de la feuille de calcul à partir d'une cellule cachée dans la Feuil2 ou quelque chose?

Je ne sais pas assez sur Excel formules de savoir quelles sont les possibilités.

TIA

InformationsquelleAutor RobertFrank | 2010-11-01
  1. 5

    Utiliser l'Adressage Indirect et les Plages Nommées

    1) Utiliser fantôme cellules (j'.e Z1...Zn) rempli avec le texte "SheetN!" pour chaque feuille que vous souhaitez supprimer.

    2) Définir des noms de plages pour tous vos adressables de l'extérieur des cellules et des plages dans les fiches,

    3) Dans vos formules de remplacer les références à "Indirecte" et le LITTÉRAL de la plage nommée.

    Exemple:

    Remplacer 

        =Sum(Sheet1!A:A)

    avec 

        =SUM(INDIRECT(Z1&"caca"))

    Où "caca" est un nom de Plage définie dans la feuille "Sheet1" en tant que:Un, et Z1 contient "Feuil1!"

    Autre Exemple:

    Remplacer 

        =Sheet1!A1+Sheet1!A2

    avec 

        =INDIRECT(Z1&"NamedRangeForA1")+INDIRECT(Z1&"NamedRangeForA2")

    Maintenant, vous pouvez supprimer la feuille "Sheet1", sans référence à des problèmes.

    Vous devez re-créer (en VBA) les Plages Nommées lorsque vous ajoutez votre nouveau la feuille Sheet1.

    Edit: pour Répondre à votre commentaire sur les Feuilles avec des espaces dans le nom

    Dans ce cas, vous devez extra-fu 🙂

    Utiliser ceci: 

     =INDIRECT("_'_" & Z1& "_'_!_" & "caca")

    Le "_" devrait être supprimé, car j'ai inséré eux là juste pour faciliter la visualisation des guillemets simples et doubles.

    Dans la cellule I1 entrez le nom de la feuille "Sheet1" sans les guillemets et sans point d'exclamation (comme c'est déjà dans la formule).

    HTH!

    • bélisaire: Grande réponse. Merci! Petite question: Plutôt que de "Sheet1", j'ai une feuille de calcul dont le nom a intégré en blanc dans le nom. Je n'ai pas été en mesure de comprendre comment utiliser les citations dans le contenu de Z1 dans votre premier exemple. Toute aide?
    • Merci beaucoup! J'ai vraiment l'apprécier. J'ai voté votre réponse jusqu'en raison de votre suivi et utile d'utiliser le trait de soulignement pour faire de votre réponse lisible!
    InformationsquelleAutor Dr. belisarius