[Excel] Verweis auf andere Datei und Tabellenblätter mit Zellinhalt herstellen

[ukyo184]

Hallo,

ich stehe vor folgendem Problem. Ich habe 12 Excel-Dateien, die exakt gleich aufgebaut sind. Diese 12 Dateien sind für die 12 Monate eines Jahres. In diesen Dateien gibt es wiederum 50 Tabellenblätter, die auch exakt gleich aufgebaut sind. Jedes Tabellenblatt steht hierbei für eine Person.

Nun möchte ich eine 13. Excel-Datei als Jahresübersicht erstellen, in der ich eine Auflistung der 50 Personen mache. Nun soll hinter jeder Person, die Summe einer bestimmten Zelle in den Tabellenblättern der Person der 12 Monatsdateien berechnet werden. Also so in der Art:



Bei der Berechnung der Summe würde ich zur Herstellung des Verweises auf das Tabellenblatt der anderen Dateien gerne die Spalte A mit benutzen. Also in den Dateien 1-12 stehen jeweils beim Tabellenblatt 1 die Daten von Gustav Gans, bei Blatt 2 von Max Mustermann, etc.

Die Summenformel sieht dann so in der Art aus:

='[01.xlsx]1'!$J$27+'[02.xlsx]1'!$J$27+'[03.xlsx]1'!$J$27+...+'[12.xlsx]1'!$J$27

Damit ich das nicht bei jeder einzelnen Zeile manuell abändern muss, suche ich nach einer Lösung in der Art:

='[01.xlsx]&A2&'!$J$27+'[02.xlsx]&A2&'!$J$27+'[03.xlsx]&A2&'!$J$27+...+'[12.xlsx]&A2&'!$J$27

Das funktioniert aber leider nicht. Weiß jemand, wie das realisierbar ist?

Vielen Dank im Voraus für eure Hilfe

Gruß ukyo

 

[Yaslaw]

Ich würde das über VBA lösen

Hier mal schnell ein Versuch. Er ist ungetestet.
Annahme: Die 12 Datein exisitieren und die entsprechenden Sheets ebenfalls.

Ein neues Modul mit dem folgenden Inhalt

Option Explicit

Private Const C_FILENAMEFORMAT = "00\.xlsx"

Private wb(1 To 12) As Workbook 'Die 12 Sheets
Private isLoaded As Boolean     'FLag ob die Sheets bereits offen sind

'/**
' * Berechnet die Summe für die entsprechende ID
' * @param  Long        ID
' * @param  String      Feldadresse
' * @param  String      Sheet-Format (Siehe F1-Hilfe für Formatbefehl in VBA): Default '0'
' * @return Double      Eine Kommazahl
' */
Public Function getSumPerId( _
        ByVal iId As Long, _
        Optional ByVal iRangeAdr As String = "$J$27", _
        Optional ByVal iSheetFormat As String = "0" _
) As Double
    Dim i As Integer
   
    'Falls die Sheets noch nicht geöffnet sind, das nachholen
    If Not isLoaded Then openBooks
    'Die Summe des Ranges ermitteln und mit den anderen zusammenzählen
    For i = 1 To 12
        getSumPerId = getSumPerId + wb(i).Worksheets(CStr(iId)).Range(iRangeAdr).Summary
    Next i
    Dim rng As Range
End Function

'/**
' * öffnet die 12 Sheets im Lesemodus
' */
Public Sub openBooks()
    Dim i As Integer
    Dim folderPath As String
    Dim filePath As String
   
    folderPath = ActiveWorkbook.Path
   
    For i = 1 To 12
        filePath = folderPath & "\" & Format(1, C_FILENAMEFORMAT)
        wb(i) = Application.Workbooks.Open(filePath, , True)
    Next i
   
    isLoaded = True
End Sub

'/**
' * Schliest die 12 Sheets
' */
Public Sub closeBooks()
    Dim i As Integer
    For i = 1 To 12
        wb(i).Close
    Next i
   
    isLoaded = False
End Sub

Und im Klassenmodu hinter deinem Workbook noch den folgenden Code um beim Schliessen die 12 anderen Books ebenfalls zu beenden

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    closeBooks
End Sub

Der Aufruf im Excelsheet sieht dann so aus

=getSumPerId(A2)

Oder falls du ein Anders Feld als $J$27 brauchst. Zum Beispiel A3 bis A10

=getSumPerId(A2; "A3:A10")

 

[ukyo184]

Hallo Yaslaw,

vielen Dank für deine schnelle Antwort. Bei VBA bin ich nicht so fit und daher weiß ich mit deiner Antwort nicht so viel anzufangen.

Gibt es denn keine Möglichkeit das mit Excel-Boardmitteln umzusetzen?

Danke und Gruß
ukyo

 

[Yaslaw]

Gibt es denn keine Möglichkeit das mit Excel-Boardmitteln umzusetzen?

VBA ist ein Excel-Boardmittel. Im Deutschen besser bekannt a "Makro"

Ich kenne keine Passende Formel. Das heisst aber nicht, dass es keine gibt. Ich schreibe ab 10 Zeichen Formel bereits ein Makro, da dies verständlicher für mich ist.