Angenommen, Sie haben eine Arbeitsmappe mit drei Arbeitsblättern, Sheet1, Sheet2 und Sheet3. In Spalte A1 des Arbeitsblatts Sheet2 haben Sie die Formel = Sheet1! A1. Wenn Sie diese Formel aus Blatt2 in Zelle A1 von Blatt3 kopieren, verweist die Formel weiterhin auf Blatt1. Wie kann das aber sein? Warum passt Excel die Blattreferenz nicht wie die Zellreferenzen an?

Wie behandelte Bereiche behandelt Excel Arbeitsblattnamen als absolut. Jedes Arbeitsblattobjekt ist unabhängig von allen anderen Arbeitsblättern in der Arbeitsmappe.

Wenn Sie eine Formel einfügen, die eine Blattreferenz enthält, bleibt diese Blattreferenz beim Einfügen unverändert.

Es gibt ein paar Dinge, die Sie tun können. Eine besteht darin, die Formelreferenz nach dem Einfügen einfach so zu ändern, dass sie auf das richtige Blatt verweist. Wenn Sie viele davon ändern müssen, können Sie alle Formeln im Zielarbeitsblatt auswählen (F5 | Spezial | Formeln) und dann mit Suchen und Ersetzen den ursprünglichen Arbeitsblattnamen (Blatt1) ersetzen

mit dem richtigen Arbeitsblattnamen (Sheet2).

Wenn Ihre Referenzierungsanforderungen nicht komplex sind, können Sie einen Makroansatz verwenden. Wenn Sie beispielsweise möchten, dass eine Formel in einer bestimmten Zelle auf eine Zelle auf dem Blatt vor dem aktuellen Blatt verweist, können Sie dies ganz einfach per Makro tun. Betrachten Sie das folgende Makro:

Function PrevSheet(rCell As Range)

Application.Volatile     Dim i As Integer     i = rCell.Cells(1).Parent.Index     PrevSheet = Sheets(i - 1).Range(rCell.Address)

End Function

Das Makro betrachtet das aktuelle Arbeitsblatt und ermittelt dann, welches Arbeitsblatt davor liegt. Der Verweis wird dann für dieses Arbeitsblatt erstellt.

Nachdem Sie das PrevSheet-Makro erstellt haben, können Sie die Funktion auf folgende Weise in einer Zelle verwenden:

=PrevSheet(A1)

Dies gibt den Wert der Zelle A1 aus dem vorherigen Arbeitsblatt zurück. Wenn Sie Sheet1, Sheet2 und Sheet3 haben und diese Formel für Sheet3 verwenden, wird der Wert von Sheet2! A1 zurückgegeben. Wenn das vorherige Blatt das erste Blatt der Arbeitsmappe ist oder kein Arbeitsblatt, gibt die Funktion einen # Value-Fehler zurück.

Wenn Sie diese Formel später auf ein anderes Blatt kopieren (z. B. zu Blatt 5), wird der Wert relativ zu seiner neuen Position abgerufen, was bedeutet, dass der Wert von Blatt 4! A1 abgerufen wird.

Sie können auch einen Blattnamen eingeben, und die Funktion funktioniert einwandfrei:

=PrevSheet(Sheet3!A5)

Diese Version gibt immer Sheet2! A5 zurück, da Sheet2 das vorherige Sheet3 ist.

_Hinweis: _

Wenn Sie wissen möchten, wie die auf dieser Seite (oder auf einer anderen Seite der ExcelTips-Websites) beschriebenen Makros verwendet werden, habe ich eine spezielle Seite vorbereitet, die hilfreiche Informationen enthält.

Klicken Sie hier, um diese spezielle Seite in einem neuen Browser-Tab zu öffnen.

ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.

Dieser Tipp (12141) gilt für Microsoft Excel 2007, 2010, 2013 und 2016.

Eine Version dieses Tipps für die ältere Menüoberfläche von Excel finden Sie hier:

Relative Arbeitsblattreferenzen.