Änderungen in Einer Excel-Datei wirken sich nicht auf die andere aus - bug report

Hallo Andreas,

schau mal hier nach dem Ordner "Bereich_Verschieben".

Dort sind zwei Quelldateien und die Zieldatei drin, einige Formeln und Anmerkungen.

Wenn man die Daten aller Quelldateien untereinander schreiben möchte, könnte man die WENNFEHLER-Sektion der Formel entsprechend umändern. Vorteilhafte wäre es, wenn dazu die Quelldateien einen Index im Namen hätten auf den man zugreifen könnte.

Edit: Das ist eigentlich nur eine Demonstration, dass es auch mit Formel gehen könnte. Aber bei großen Datenbestand, die nur durch Array-Formeln ausgelesen werden können, würde ich VBA vorziehen.

Claus

Da sich ZEILE beim Herunterziehen ändert, kann man diese Formel einfach nach unten kopieren.

Hallo Claus.

Bis hierhin alles klar. Wie weit kopiere ich nach unten?

Ich ziehe jetzt sagen wir mal bis Zeile 100.

(Bei mehreren Dateien ändere ich den Namen der Quelldatei auf die nächste. Wieder 100 Zeilen, nächste Quelldatei, etc.)

Solange <= 100 Zeilen in jeder Quelldatei drin sind... die Nullen könnte man zur Not mit Hilfsspalte wegfiltern.

Nun schreibe ich in irgendeiner Quelldatei in die 101 Zeile was rein... vorbei mit der Formelzauberei?

Da liegt mein Verständnisproblem: Was Du schreibst ließt sich als wenn es da eine Möglichkeit gibt automatisch alle Daten zu erfassen.

Ich sehe das so: Kopiere ich die Formel 100 Zeilen runter, bekomme ich die ersten 100 Datensätze, alles ab dem 101 nicht. Und es wird mir auch nicht angezeigt das mir was fehlt. Hat die Quelle weniger als 100 Datensätze bekomme ich leere Zellen als 0 angezeigt, also "Datenmüll".

EDIT: Klingt zu hart, für eine Formel basierende Lösung ist das schon gut.

Andreas.

Hallo Chris,

jetzt muss ich mal nachfragen, ob ich etwas falsch verstehe.

Lutz hat in seiner Zieldatei unkorrekte Daten, weil sich in der geschlossenen Zieldatei die Zellbezüge nicht ändern, wenn in einer Quelldatei Spalten oder Zeilen eingefügt oder gelöscht werden.

Müsste zum Beheben dieses Fehlers nicht in jede Quelldatei ein Code, der beim Öffnen die Zieldatei mit öffnet, damit diese die Änderungen an der Struktur mitbekommt?

Claus

Hallo Lutz,

Edit: :)

Oder ganz ohne VBA als Aufgabenbereich ablegen.

https://www.youtube.com/watch?v=QA46kmZNzc8

hier mal die Alternative mit VBA

mit dem Nachteil das es Mobil nicht funktionieren würde!

Der Code müsste in "DieseArbeitsmappe" deines "Masterfiles"

denke aber daran das die Datei nun die Endung .xlsm haben muss. Deine bestehenden Verknüpfungen aber sicherlich noch auf xlsx sind.

Option Explicit

'##################################

'anpassen wenn die Dateien in verschiedenen Ordnern liegen müsste der Code ein wenig umgebaut werden.

Const strPath As String = "P:\Temp"

Dim arrFiles

'#################################

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim i           As Integer

Dim objWB       As Object

'###################

'Achtung beim Schließen wird auch gespeichert!

ThisWorkbook.Save

'##################

For i = 0 To UBound(arrFiles)

     Set objWB = fCheckFile(strPath & arrFiles(i))

    If Not objWB Is Nothing Then

        objWB.Close True

    End If

Set objWB = Nothing

Next

End Sub

Private Sub Workbook_Open()

Dim i           As Integer

Dim objWB       As Object

'#################################

'anpassen

arrFiles = Array("FileA.xlsx", "FileB.xlsx", "FileC.xlsx", "FileD.xlsx", "FileE.xlsx")

'#################################

For i = 0 To UBound(arrFiles)

     Set objWB = fCheckFile(strPath & arrFiles(i))

     If objWB Is Nothing Then

         Set objWB = Workbooks.Open(strPath & arrFiles(i))

         If objWB.ReadOnly Then

            MsgBox "Achtung! Mappe: " & objWB.Name & " ist schreibgeschützt.!" & Chr(10) & _

            "Änderungen werden in dieser Mappe nicht berücksichtigt, diese Mappe wird jetzt geschlossen!"

            objWB.Close False

         End If

     End If

Set objWB = Nothing

Next

ThisWorkbook.Activate

End Sub

Private Function fCheckFile(strFullPath As String) As Workbook

     Dim objWB As Workbook

     For Each objWB In Application.Workbooks

         If objWB.FullName = strFullPath Then

             Set fCheckFile = objWB

             Exit For

         End If

     Next

 End Function

Hallo Andreas,

in der Quelldatei (A) vergebe z.B. den Namen "Daten" und schreibe bei "Bezieht sich auf":

=BEREICH.VERSCHIEBEN($A$1;;;ANZAHL2($A:$A);ANZAHL2($1:$1))

Dieser vergebene Namen beinhaltet nun die Matrix und du kannst diesen Namen verwenden wie z.B. A1:G100.

Somit brauchst du in der Zieldatei (B) um diese Matrix nur die INDEX-Formel zu ranken, um die entsprechenden Daten daraus zu holen:

=INDEX(A.xlsx!Daten;ZEILE(A2);VERGLEICH("Vorname";INDEX(A.xlsx!Daten;1;0);0))

für den ersten Wert unterhalb der Überschrift "Vorname".

Da sich ZEILE beim Herunterziehen ändert, kann man diese Formel einfach nach unten kopieren.

Den Namen Daten kann er für jede Quellmappe mit der gleichen Formel benutzen. In der Formel in der Zielmappe muss dann nur der entsprechende Mappenname vorgestellt werden.

Wenn du damit nicht klar kommst, melde dich wieder, damit ich ein Beispiel erstellen kann. Dazu komme ich aber erst gegen Mittag.

Claus