Dynamische Arbeitsblatt- oder Arbeitsmappenreferenz in Excel

Angenommen, Sie haben Daten im gleichen Format über mehrere Arbeitsblätter oder Arbeitsmappen verteilt und möchten diese Daten dynamisch in ein anderes Blatt einfügen. Die INDIREKT-Funktion kann Ihnen dabei helfen, dies schnell 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 Zusammenfassungsblatt erstellen, um die Quartalsverkäufe basierend auf dem entsprechenden Verkäufer dynamisch abzurufen. Um dies zu ermöglichen, kann die folgende Formel helfen.

Allgemeine Formel

=INDIREKT("'"&sheet_name&"'!Zelle aus der Daten zurückgegeben werden sollen")

1. Wie im folgenden Screenshot gezeigt, müssen Sie zunächst das Zusammenfassungsblatt erstellen, indem Sie die Blattnamen separat in verschiedene Zellen eingeben. Wählen Sie dann eine leere Zelle aus, kopieren Sie die folgende Formel hinein und drücken Sie die Eingabetaste.

=INDIREKT("'"&B3&"'!C3")

Hinweise: Im Code:

• B3 ist die Zelle, die den Namen des Blatts enthält, aus dem Sie Daten abrufen möchten;
• C3 ist die Zelladresse im spezifischen Arbeitsblatt, aus dem Sie die Daten abrufen möchten;
• Um zu verhindern, dass ein Fehlerwert zurückgegeben wird, wenn entweder B5 (die Zelle mit dem Blattnamen) oder C3 (die Zelle, aus der Sie die Daten abrufen) leer ist, schließen Sie bitte die INDIREKT-Formel in eine WENN-Funktion wie folgt ein:
  =WENN(ODER(B3="";C3=""");"";INDIREKT($B$3&"!C3"))
• Wenn es keine Leerzeichen in Ihren Blattnamen gibt, können Sie diese Formel direkt verwenden:
  =INDIREKT(B3&"!C3")

2. Ziehen Sie dann den Ausfüllkursor nach unten, um die Formel auf andere Zellen anzuwenden. Nun haben Sie alle Verkäufe des ersten Quartals aus den jeweiligen Arbeitsblättern zurückgegeben.

3. Fahren Sie fort, alle Verkäufe der anderen Quartale nach Bedarf abzurufen. Und vergessen Sie nicht, die Zellreferenz in der Formel zu ändern.

Zellen in einer anderen Arbeitsmappe dynamisch referenzieren

Dieser Abschnitt behandelt das dynamische Referenzieren von Zellen in einer anderen Arbeitsmappe in Excel.

Allgemeine Formel

=INDIREKT("'[" & Buchname & "]" & Blattname & "'!" & Zelladresse)

Wie im folgenden Screenshot gezeigt, befinden sich die Daten, die Sie zurückgeben möchten, in Spalte E des Arbeitsblatts „Total sales“ in einer separaten Arbeitsmappe namens „SalesFile“. Bitte gehen Sie Schritt für Schritt wie folgt vor, um dies zu erledigen.

1. Zuerst füllen wir die Arbeitsmappeninformationen aus (einschließlich des Arbeitsmappennamens, des Arbeitsblattnamens und der zu referenzierenden Zellen), aus denen Sie die Daten in die aktuelle Arbeitsmappe einfügen möchten.

2. Wählen Sie eine leere Zelle aus, kopieren Sie die folgende Formel hinein und drücken Sie die Eingabetaste.

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

Hinweise:

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

3. Ziehen Sie dann den Ausfüllkursor nach unten, um die Formel auf andere Zellen anzuwenden.

Tipp: Wenn Sie nicht möchten, dass der Rückgabewert nach dem Schließen der referenzierten Arbeitsmappe zu einem Fehler wird, können Sie den Namen der Arbeitsmappe, den Blattnamen und die Zelladresse direkt in der Formel wie folgt angeben:
=INDIREKT('[SalesFile.xlxs]Total sales'!E3;"")

Verwandte Funktion