Dynamische Arbeitsblatt- oder Arbeitsmappenreferenz in Excel

Angenommen, Sie haben Daten im gleichen Format über mehrere Arbeitsblätter oder Arbeitsmappen verteilt und möchten diese dynamisch in ein anderes Blatt übertragen – dann hilft Ihnen die INDIREKT-Funktion, dies schnell und effizient zu erledigen.

Zellen in einem anderen Arbeitsblatt dynamisch referenzieren
Zellen in einer anderen Arbeitsmappe dynamisch referenzieren

Zellen in einem anderen Arbeitsblatt dynamisch referenzieren

Angenommen, es gibt vier Arbeitsblätter mit unterschiedlichen Quartalsverkäufen für vier Verkäufer, und Sie möchten ein Zusammenfassungsarbeitsblatt erstellen, um die Quartalsverkäufe basierend auf dem jeweiligen Verkäufer dynamisch abzurufen. Um dies zu erreichen, kann die folgende Formel helfen.

Allgemeine Formel

=INDIRECT(")'„&sheet_name&"'!Cell to return data from")

1. Wie im folgenden Screenshot gezeigt, erstellen Sie zunächst das Zusammenfassungsarbeitsblatt, indem Sie die Arbeitsblattnamen jeweils in separate Zellen eingeben. Wählen Sie anschließend eine leere Zelle aus, fügen Sie die folgende Formel ein und drücken Sie die Eingabetaste.

=INDIRECT("'"&B3&„'!C3")

Hinweise: Im Code:

• B3ist die Zelle, die den Arbeitsblattnamen enthält, aus dem Sie Daten abrufen werden;
• C3ist die Zelladresse im jeweiligen Arbeitsblatt, aus der Sie deren Daten abrufen werden;
• Um zu verhindern, dass ein Fehlerwert zurückgegeben wird, wenn entweder B5 (die Zelle mit dem Arbeitsblattnamen) oder C3 (die Zelle, aus der Sie die Daten abrufen) leer ist, schließen Sie bitte die INDIREKT-Formel in eine WENN-Funktion ein, wie unten dargestellt:
  =IF(OR(B3="",C3=""),"",INDIRECT($B$3&"!C3"))
• Wenn Ihre Arbeitsblattnamen keine Leerzeichen enthalten, können Sie diese Formel direkt verwenden
  =INDIRECT(B3&„!C3")

2. Ziehen Sie anschließend den Ausfüllkästchen-Griff nach unten, um die Formel auf andere Zellen anzuwenden. Jetzt haben Sie alle Verkäufe des ersten Quartals aus den entsprechenden Arbeitsblättern abgerufen.

3. Fahren Sie fort, um bei Bedarf alle Verkäufe der weiteren Quartale abzurufen. Vergessen Sie nicht, die Zellreferenz in der Formel entsprechend anzupassen.

Zellen in einer anderen Arbeitsmappe dynamisch referenzieren

Dieser Abschnitt zeigt Ihnen, wie Sie in Excel Zellen aus einer anderen Arbeitsmappe dynamisch referenzieren können.

Allgemeine Formel

=INDIRECT(")'[" & Book name & "]„ & Sheet name & "'!" & Cell address)

Wie im folgenden Screenshot gezeigt, befinden sich die Daten, die Sie abrufen möchten, in Spalte E des Arbeitsblatts „Gesamtverkäufe“ in einer separaten Arbeitsmappe mit dem Namen „Verkaufsdatei“. Gehen Sie Schritt für Schritt wie folgt vor, um dies zu erreichen.

1. Geben Sie zunächst die Arbeitsmappeninformationen ein – einschließlich Arbeitsmappenname, Arbeitsblattname und Referenzzellen –, auf deren Grundlage Sie Daten in die aktuelle Arbeitsmappe abrufen möchten.

2. Wählen Sie eine leere Zelle aus, fügen Sie die folgende Formel ein und drücken Sie die Eingabetaste.

=INDIRECT("'["&$B$3&"]"&$C$3&„'!"&D3)

Hinweise:

• B3enthält die Arbeitsmappenname, aus der Sie die Daten extrahieren möchten;
• C3ist der Arbeitsblattname;
• D3ist die Zelle, aus der Sie Daten abrufen werden;
• Der #BEZUG!-Fehlerwert wird zurückgegeben, wenn die referenzierte Arbeitsmappe geschlossen ist;
• Um den #BEZUG!-Fehlerwert zu vermeiden, schließen Sie bitte die INDIREKT-Formel in die WENNFEHLER-Funktion ein, wie folgt:
  =IFERROR(INDIRECT("‚["&$B$3&"]"&$C$3&"'!"&D3),„")

3. Ziehen Sie anschließend das Ausfüllkästchen nach unten, um die Formel auf weitere Zellen anzuwenden.

Tipp:Wenn Sie nicht möchten, dass die Rückgabewert nach dem Schließen der referenzierten Arbeitsmappe zu einem Fehler wird, können Sie stattdessen direkt die Arbeitsmappenname, Arbeitsblattname und die Zelladresse in der Formel angeben, wie hier:
=INDIRECT(‚[SalesFile.xlxs]Total sales'!E3,„")

Verwandte Funktion