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

Hallo Lutz,

dieses Verhalten hatte ich nicht solange ich in den richtigen Dateien am richtigen Speicherort gearbeitet habe. Erst nachdem ich die Dateien verschoben habe, ist dieser Fehler aufgetreten.

Es scheint also, dass das Ganze nicht verlässlich arbeitet. Ich rate dir auf die Makrolösung umzusteigen.

Claus

Hallo Claus,

wow, JETZT verstehe ich erst! Okay, das ist clever, Respekt!

Habe das entsprechend geändert, funktioniert ebenso gut - aber schneller ;))

Allerdings ändert das nichts an meinem Problem, ganz im Gegenteil: Wenn ich nur die Zieldatei öffne, nicht aber die Quelldatei, werden die zu ziehenden Daten nicht gezogen und somit auch nicht angezeigt.

Schlimmer noch: Wenn ich jetzt beim Start der Zieldatei auf "aktualisieren" klicke, erhalte ich folgende Fehlermeldung:

"Einige der Verknüpfungen in Ihrer Arbeitsmappe lassen sich zurzeit nicht aktualisieren."

Klicke ich dann auf "Verknüpfungen bearbeiten", steht in der Zeile der Quelldatei: "Fehler: Der Name ist nicht definiert oder bezieht sich nicht auf einen rechtwinkligen Bereich."

Sobald ich die Quelldatei öffne, ist alles OK.

Ist das bei Dir auch so?

Die Berechnung des Tabellenbereichs in der Quelldatei ist aber korrekt: Wenn ich dort im Namensmanager auf den entsprechenden Eintrag gehe, markiert er den Tabellenbereich korrekt.

Klicke ich beim Start auf "nicht aktualisieren", erhalte ich in den betreffenden Zeile den Fehler #BEZUG

Falls das bei Dir nicht so ist: Hast Du eine Idee, warum Excel sich bei mir nicht die alten Bezugsdaten merkt?

Denn wenn ich "nicht aktualisieren" klicke, müsste Excel doch eigentlich zumindest den letztbekannten Stand der Werte und Bezüge anzeigen - derlei hatte ich noch nie ...

Es muss etwas mit der Verwendung von BEREICH.VERSCHIEBEN zu tun haben, denn ich hatte Dir ja meine alte Formel mit den "unendlichen" Tabellenmatrix für die INDEX-Funktion geschickt, da hatte ich das Problem noch nicht. Erst, seit ich BEREICH.VERSCHIEBEN verwende ...

Beste Grüße,

Lutz

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.