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

Hallo Lutz,

erstens solltest du mit BEREICH.VERSCHIEBEN nur einen Bereichsnamen für die Daten anlegen. Nicht diese Formel in allen Zellen verwenden, das macht nur Schwierigkeiten.

Was ist mit diesem Term mit Zeile? In Zeile sollte eigentlich eine Zahl stehen oder auf eine Zahl verwiesen werden.

Gibt dieser Ausdruck "'E:\OneDrive\Arbeit\Handy-Tests\Z_Handytest-DB.xlsm'!Gesamtpunktzahl" eine Zahl zurück? Außerdem sollte die Ausgangszelle dann $A$3 sein, wenn die Daten erst in Zeile 3 beginnen.

Ich weiß nicht wie deine Daten vorliegen und kann daher auch nicht sagen wo der Fehler liegt.

Ich habe für Andreas einen Ordner mit 2 Quelldateien und der Zieldatei nach OneDrive hochgeladen und dort ist auch der Fehler aufgetreten. Wenn man die Namen und die Formeln in den richtigen Dateien und am richtigen Speicherort einfügt, ist alles in Ordnung. Wenn man die Dateien dann verschiebt, tritt der Fehler auf.

Schau mal hier nach dem Ordner "Bereich_Verschieben" und lade ihn dir herunter. Sehr wahrscheinlich bekommst du dann den gleichen Fehler wie Andreas, aber du kannst in den Quellmappen sehen, wie der Name angelegt ist und in der Zielmappe, wie die Formel gestaltet ist.

BEREICH.VERSCHIEBEN hat folgende Argumente:

Ausgangszelle, Versatz in Zeilenrichtung, Versatz in Spaltenrichtung, Höhe in Zeilenrichtung, Breite in Spaltenrichtung

Wenn also eine Tabelle in A1 anfängt, lautet die Formel

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

Nach der Ausgangszelle kommen drei Semikolon ohne Werte dazwischen, da es keinen Versatz in den Richtungen gibt. Dann kommt ANZAHL2($A:$A), das die Höhe durch zählen der belegten Zellen ermittelt und ANZAHL2($1:$1) das die Breite ermittelt.

Wenn deine Daten an anderer Stelle anfangen, musst du auch mit dem Versatz arbeiten. Du musst auch bedenken, dann der Term mit ANZAHL2 alle Zellen zählt. Wenn du also noch Daten in der entsprechenden Zeile oder Spalte hast außerhalb der gewünschten Tabelle, musst du diese Zellen abziehen. Der Bereich wird auch nicht korrekt beschrieben, wenn du leere Zeilen oder Spalten in der Tabelle hast. In diesem Fall müsste man ANZAHL2 mit einer Funktion ersetzen, die die letzte belegte Zelle in der Spalte oder der Zeile ermittelt.

Erstelle mit BEREICH.VERSCHIEBEN nur den Bereichsnamen. Du kannst dann im Namensmanager den Namen anwählen und den Cursor in diese Formel stellen. Der Bereich, den der Namen beschreibt, bekommt dann einen Laufrahmen. Daran kannst du dann sehen, ob die Formel den korrekten Bereich umschreibt.

Da das Ganze nicht ganz so einfach ist, wollte ich von dir ja auch wenigstens zwei abgespeckte Beispielmappen.

Willst du dich nicht mit der VBA-Lösung vertraut machen? Das ist nicht so schwierig wie du das beschrieben hast. Solch ein "normales" Makro läuft in allen Excel-Versionen.

Claus

Hallo Claus,

jetzt stehe ich vor einem Problem: Nachdem Deine Formeln auch mit BEREICH.VERSCHIEBEN prima funktioniert haben, kopierte ich die in die Original-Datei. Die weist nun ein Phänomen auf: Die Ergebnisse sind in der Zieldatei nur sichtbar, wenn die Quelldatei geöffnet ist. Auch wenn ich beim Öffnen der Zieldatei "aktualisieren" klicke. 

Die Verknüpfungen sehen gut aus, und natürlich ist in den Optionen aktiv, dass Daten aus anderen Dokumenten übernommen bzw. aktualisiert werden sollen.

Ist das normal bei Verwendung von BEREICH.VERSCHIEBEN?

Oder habe ich da irgendwo einen Fehler drin?

Die Ergebnisse sind jedenfalls korrekt, auch die Sache mit dem Hinzufügen oder Löschen von Spalten oder Zeilen während die Zieldatei geschlossen ist klappt prima. Eigentlich also alles gut - aber ich möchte halt nicht gezwungen sein, stets aller 5 Dateien zu öffnen, das war ja der Ausgangspunkt meines Dilemmas ;))

Hier ein Beispiel für eine der Formeln, die ich da jetzt verwende:

=WENNFEHLER(INDEX(BEREICH.VERSCHIEBEN('E:\OneDrive\Arbeit\Handy-Tests[Z_Handytest-DB.xlsm]Handy-DB'!$A$1;;;ZEILE('E:\OneDrive\Arbeit\Handy-Tests\Z_Handytest-DB.xlsm'!Gesamtpunktzahl);ANZAHL2('E:\OneDrive\Arbeit\Handy-Tests[Z_Handytest-DB.xlsm]Handy-DB'!$3:$3)*3);ZEILE('E:\OneDrive\Arbeit\Handy-Tests\Z_Handytest-DB.xlsm'!Punkte_Ausstattung);VERGLEICH($B799; 'E:\OneDrive\Arbeit\Handy-Tests[Z_Handytest-DB.xlsm]Handy-DB'!$3:$3;0)+2);"")

"Z_Handytest-DB.xlsm" ist diejenige Datei, aus der die übernommenen Daten stammen.

B799 ist die Zeile, aus der ich die Formel herauskopiert habe, da steht die Bezeichnung drin (nicht in Spalte A) - in der Quelldatei finden sich die Bezeichnungen in Zeile 3, daher "$3:$3" und nicht "$1:$1"  ;))

Siehst Du da irgendeinen Fehler?

Oder woran liegt es, dass ich jetzt die Ergebnisse nicht mehr "offline" sehe?

Beste Grüße,

Lutz

solange ich die Dateien auf meinem PC erstellt und gespeichert hatte, war alles korrekt. Dieser Fehler kommt scheinbar durch das Verschieben nach OneDrive. Features, die online nicht unterstützt werden, werden beim Herunterladen entfernt.

Hallo Claus,

dann ist jetzt alles klar, ich danke Dir.

Andreas.

Hallo Andreas,

solange ich die Dateien auf meinem PC erstellt und gespeichert hatte, war alles korrekt. Dieser Fehler kommt scheinbar durch das Verschieben nach OneDrive. Features, die online nicht unterstützt werden, werden beim Herunterladen entfernt.

Diesen Fehler, den du hast, habe ich in den OneDrive-Daten auch. Aber die Fehlermeldung ist nicht aussagekräftig, da eindeutig falsch. Der Name ist sowohl definiert und der Bereich ist auch rechteckig.

Wenn du auf Daten => Verknüpfung bearbeiten gehst und dann die beiden Quelldateien anwählst und auf Quelle öffnen gehst, sind die Daten da. Sobald dann die Quelldateien wieder geschlossen werden, kommt wieder der Fehler.

Da neuerdings auch viel online gearbeitet wird, ist das also keine vertrauenswürdige Lösung.

Claus

schau mal hier nach dem Ordner "Bereich_Verschieben".

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.

Hallo Claus,

Alles gut, ich möcht ja nur gerne dem Ding mal auf den Grund gehen.

Also Zip hab ich, alles in ein Verzeichnis entpacken, kuck ich in QuellA.xlsx, okay Name "Daten" sieht gut aus, wieder zu, QuellB.xlsx dito., auch wieder zu.

Ziel.xlsx auf:

Auch wenn ich im 2ten Schritt auf "Weiter" klicke ist das Resultat:

Formel sieht doch gut aus... Pfad passt, da liegen alle Dateien, Z: ist meine RAM-Disk.

Andreas.