Autovervollständigung über mehrere Tabellenblätter

Forumthread: Autovervollständigung über mehrere Tabellenblätter

28.04.2016 10:13:33

Dennis

Hallo zusammen,
ich habe eine excelbezogene Fragestellung (ach was) nur weiß ich nicht recht wie ich sie formulieren soll, folglich fällt es mir schwer nach bestehenden Themen zu suchen.
Hier ist die eigentlich recht simple Aufgabenstellung:
Ich erstelle eine neue Tabelle in einer leeren Arbeitsmappe. Eine Spalte der Tabelle soll Werte abbilden welche in einer anderen Arbeitsmappe auf mehreren Tabellen, allerdings immer in der gleichen Zelle, eingetragen sind.
Die Formel ist demnach ein simples "='[Datei.xls]TabelleX'!$D$10" - nun wäre es aber schön wenn ich beim automatischen Vervollständigen meiner neuen Tabelle den Zellenbezug in die "dritte Dimension" erweitern könnte, etwa so:
A1='[Datei.xls]Tabelle1'!$D$10"
A2='[Datei.xls]Tabelle2'!$D$10"
A3='[Datei.xls]Tabelle3'!$D$10"
usw.
Nun könnte ich die Formel natürlich manuell in jede Zelle schreiben, doch es handelt sich leider um sehr große, durch eine Software aufgezeichnete Datensätze und die untertabellen sind auch nicht logisch durchnummeriert (die "TabelleX" aus "Datei.xls" haben alle individuelle Bezeichnungen). Es wäre daher ideal wenn die Formel in der Lage wäre auf die in der Tabellenlogik nächste Tabelle zu springen, also von "TabelleX" zu "TabelleX+1" wie in der Arbeitsmappenansicht angeordnet. Wenn es jedoch eine Lösung gibt logische Tabellenbezeichnungen automatisch mit einem "+1" referenzieren zu lassen könnte ich zur Not die untertabellen umbenennen. Das wäre immernoch weniger Arbeit als die Daten manuell zu übertragen :)
Ich hoffe ich habe meine Fragestellung verständlich ausgedrückt, falls nicht bitte ich um einen Hinweis :)

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Autovervollständigung über mehrere Tabellenblätter

29.04.2016 00:01:10

fcs

Hallo Dennis,
der einfachste Weg dürfte en keines Makro sein.
Wenn sich die Daten in den Quelltabellen nie mehr ändern, dann ist es sinnvoller die Werte aus der Zelle D10 in den Blättern zu überehmen, statt Formeln einzufüen.

'Dieses Makro in der persönlichen Makroarbeitsmappe in einem allgemeinen Modul speichern.
'Vor dem Start des Makros die Datei mit den Quelldaten öffnen - Die Datei mit den Quelldaten _
muss die aktive Arbeitsmappe sein, wenn das Makro gestartet wird
'Dann Makro starten.
'Das Makro legt eine neue Arbeitsmappe an und trägt die Formeln ein.
Sub GetData_aus_D10()
Dim Zeile As Long
Dim rngZelle As Range
Dim wksZ As Worksheet
Dim wkbQ As Workbook, wksQ As Worksheet
Dim sZelleR1C1 As String
Set wkbQ = ActiveWorkbook
Application.Workbooks.Add Template:=xlWBATWorksheet
Set wksZ = ActiveSheet
Set rngZelle = wksZ.Cells(2, 1) 'Zelle A2 - Zelle ab der die Formeln eingefügt werden  _
sollen
sZelleR1C1 = "R10C4" ' Zelle $D$10 - Zelle deren Werte ausgelesen werden sollen in _
R1C1-Schreibweise
For Each wksQ In wkbQ.Worksheets
rngZelle.Offset(Zeile, 0).FormulaR1C1 = "='[" & wkbQ.Name & "]" & wksQ.Name & "'!" &  _
sZelleR1C1
'        rngZelle.Offset(Zeile, 0).Value= wksQ.Range("D10").Value  'Wert aus Quelle übernehmen
Zeile = Zeile + 1
Next
End Sub

Gruß
Franz

AW: Autovervollständigung über mehrere Tabellenblätter

29.04.2016 12:05:51

Dennis

Hallo Franz,
vielen Dank für diese Lösung, sie funktioniert fantastisch und lässt sich relativ einfach modifizieren um wetiere Zellen auszulesen. Schade ist nur, dass ich durch fehlende VBA Kenntnisse nicht wirklich verstehe wie es funktioniert, aber ich weiß zumindest wie ich dieses Makro anwenden muss um meinen Anforderungen zu genügen :)
Es ist nur erstaunlich dass Excel keine eigene Lösung für eine solche Aufgabenstellung bietet.

Infobox / Tutorial

Autovervollständigung über mehrere Tabellenblätter in Excel

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Erstelle eine neue Arbeitsmappe: Öffne Excel und erstelle eine neue Arbeitsmappe, in der Du die Werte aus anderen Tabellenblättern konsolidieren möchtest.
Öffne die Quellarbeitsmappe: Stelle sicher, dass die Arbeitsmappe mit den Daten, die Du abrufen möchtest, geöffnet ist.

Füge das Makro ein: Gehe zu Entwicklertools > Visual Basic, füge das folgende Makro in ein allgemeines Modul ein:

Sub GetData_aus_D10()
   Dim Zeile As Long
   Dim rngZelle As Range
   Dim wksZ As Worksheet
   Dim wkbQ As Workbook, wksQ As Worksheet
   Dim sZelleR1C1 As String
   Set wkbQ = ActiveWorkbook
   Application.Workbooks.Add Template:=xlWBATWorksheet
   Set wksZ = ActiveSheet
   Set rngZelle = wksZ.Cells(2, 1) 'Zelle A2 - Zelle ab der die Formeln eingefügt werden sollen
   sZelleR1C1 = "R10C4" ' Zelle $D$10 - Zelle deren Werte ausgelesen werden sollen in R1C1-Schreibweise
   For Each wksQ In wkbQ.Worksheets
       rngZelle.Offset(Zeile, 0).FormulaR1C1 = "='[" & wkbQ.Name & "]" & wksQ.Name & "'!" & sZelleR1C1
       Zeile = Zeile + 1
   Next
End Sub

Starte das Makro: Schließe den VBA-Editor und gehe zurück zu Excel. Starte das Makro über Entwicklertools > Makros, wähle GetData_aus_D10 und klicke auf Ausführen.
Überprüfe die Ergebnisse: Deine neue Arbeitsmappe sollte nun die Werte aus Zelle D10 aller Tabellenblätter der Quellarbeitsmappe enthalten.

Häufige Fehler und Lösungen

Makro funktioniert nicht: Stelle sicher, dass die Quellarbeitsmappe geöffnet und aktiv ist, bevor Du das Makro startest.
Falsche Zellreferenzen: Überprüfe, ob die Zellreferenzen in der Formel korrekt sind. Die Zelle D10 muss in jedem Tabellenblatt die gewünschten Werte enthalten.
Excel-Sicherheitseinstellungen: Möglicherweise blockiert Excel die Ausführung von Makros. Ändere die Sicherheitseinstellungen unter Datei > Optionen > Trust Center > Trust Center-Einstellungen > Makroeinstellungen.

Alternative Methoden

Wenn Du kein Makro verwenden möchtest, kannst Du auch die Funktion INDIREKT() nutzen, um auf Werte in anderen Tabellenblättern zuzugreifen. Hier ist ein Beispiel:

=INDIREKT("'[Datei.xls]" & "Tabelle1'!$D$10")

Diese Methode kann jedoch umständlich sein, wenn Du viele Tabellenblätter hast, da Du die Formel für jedes Blatt manuell anpassen musst.

Praktische Beispiele

Angenommen, Du hast die folgenden Tabellenblätter in einer Arbeitsmappe:

Tabelle1
Tabelle2
Tabelle3

Um die Werte von D10 in A2, A3 und A4 in einer neuen Tabelle zu übernehmen, kannst Du das Makro wie oben beschrieben verwenden. Alternativ kannst Du folgende INDIREKT()-Formeln nutzen:

A2: =INDIREKT("'[Datei.xls]Tabelle1'!$D$10")
A3: =INDIREKT("'[Datei.xls]Tabelle2'!$D$10")
A4: =INDIREKT("'[Datei.xls]Tabelle3'!$D$10")

Tipps für Profis

Makros anpassen: Du kannst das Makro so anpassen, dass es auch andere Zellwerte abruft oder mehrere Zellen gleichzeitig verarbeitet.
Fehlerbehandlung einfügen: Füge einen Fehlerbehandlungsmechanismus in Dein Makro ein, um sicherzustellen, dass es nicht abstürzt, wenn eine Tabelle keine Daten enthält.
Dokumentation: Halte Deine Makros gut dokumentiert, damit Du oder andere Benutzer sie leicht verstehen und anpassen können.

FAQ: Häufige Fragen

1. Kann ich die Autovervollständigung Excel auch für andere Zellen verwenden?
Ja, Du kannst die Zellreferenz im Makro oder in der INDIREKT()-Formel anpassen, um Werte aus anderen Zellen abzurufen.

2. Was mache ich, wenn ich eine Fehlermeldung erhalte?
Überprüfe die Zellreferenzen und stelle sicher, dass die Quellarbeitsmappe geöffnet ist und die Tabellenblätter vorhanden sind. Achte auch auf die Sicherheitseinstellungen für Makros in Excel.

Beliebteste Forumthreads (12 Monate)

Autovervollständigung über mehrere Tabellenblätter

Forumthread: Autovervollständigung über mehrere Tabellenblätter

2
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Infobox / Tutorial

Autovervollständigung über mehrere Tabellenblätter in Excel

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

Autovervollständigung über mehrere Tabellenblätter

Forumthread: Autovervollständigung über mehrere Tabellenblätter

2 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

Autovervollständigung über mehrere Tabellenblätter in Excel

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

2
Beiträge zum Forumthread

Beiträge zu diesem Forumthread