Wie kann der Verweis einer Formel auf ein Arbeitsblatt beibehalten werden, wenn das Arbeitsblatt gelöscht und ersetzt wird?

Question

Ich bin der Autor einer Anwendung, die vor dem Öffnen einer Excel-Arbeitsmappe einige Arbeitsblätter löscht und neu erstellt, wie z. B. "Sheet1" im folgenden Beispiel.

Andere Arbeitsblätter (z. B. Blatt2) in derselben Arbeitsmappe können Formeln enthalten, die sich auf das ersetzte Arbeitsblatt beziehen, z. B. so:

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

Leider wird der Verweis in der obigen Formel auf Blatt1 unterbrochen, wenn das Blatt ersetzt wird, und wird zu

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

Fällt jemandem eine Möglichkeit ein, diese Formel so zu kodieren, dass sie nicht fehlschlägt? Sie könnte zum Beispiel den Namen des Arbeitsblatts aus einer verborgenen Zelle in Blatt2 abrufen oder ähnliches?

Ich weiß einfach nicht genug über Excel-Formeln, um zu wissen, welche Möglichkeiten es gibt.

TIA

Answer 1

Indirekte Adressierung und benannte Bereiche verwenden

1) Verwenden Sie Phantomzellen (z.B. Z1...Zn) mit dem Text "BlattN!" für jedes Blatt, das Sie löschen möchten.

2) Definieren Sie Bereichsnamen für alle Ihre extern adressierbaren Zellen und Bereiche in diesen Blättern

3) Ersetzen Sie in Ihren Formeln die Referenzen durch "Indirekt" und das LITERAL des genannten Bereichs.

Exemple :

Ersetzen Sie

    =Sum(Sheet1!A:A)   

mit

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

Dabei ist "caca" ein benannter Bereich, der in Sheet1 als A:A definiert ist, und Z1 enthält "Sheet1!"

Ein weiteres Beispiel:

Ersetzen Sie

    =Sheet1!A1+Sheet1!A2

mit

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

Jetzt können Sie das Blatt1 ohne Referenzprobleme löschen.

Sie sollten die benannten Bereiche (in VBA) neu erstellen, wenn Sie Ihr neues Blatt1 hinzufügen.

Edit: Antwort auf Ihren Kommentar zu Blättern mit Leerzeichen im Namen

In diesem Fall brauchen Sie Extra-Fu :)

Verwenden Sie dies:

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

Die "_" sollten entfernt werden, da ich sie nur eingefügt habe, um die Visualisierung der einfachen und doppelten Anführungszeichen zu erleichtern.

In die Zelle Z1 geben Sie den Namen von Blatt1 ohne Anführungszeichen und ohne Ausrufezeichen ein (wie er bereits in der Formel steht).

HTH!