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

Hallo Andreas,

wenn du für jeden Wert, den du aus der Quelltabelle auslesen willst, diese Formel verwendest, ist das natürlich der Overkill. Aber beschreibe mit BEREICH.VERSCHIEBEN den Tabellenbereich und vergebe diesem einen Namen und nutze diesen in der Array-Formel. Da sollte sich die Dateigröße im Rahmen halten.

Wenn du die Daten als Tabelle formatierst, bekommt diese Tabelle auch automatisch einen Namen. Wenn es die erste auf dem Blatt ist "Tabelle1". Darauf kann man auch zurückgreifen, denn dieser Namen passt sich immer der aktuellen Tabellengröße an. Ich persönlich bevorzuge den dynamischen Bereichsnamen, da ich finde, dass als Tabelle formatierte Daten die Mappe langsamer machen.

Das nur zur Information. Du weißt, dass ich nichts gegen Makros habe oder sage. Ich will nur, dass jeder alle Möglichkeiten ausloten kann und dann für sich selbst bestimmen kann, was für ihn das Beste ist.

Claus

=INDEX(BEREICH.VERSCHIEBEN($A$1;;;ANZAHL2($A:$A);ANZAHL2($1:$1));ZEILE(A2);VERGLEICH("Vorname";$A$1:$F$1;0))

Scherz: Ich wette da meckert der Lutz "ich hab auch noch was hinter F stehen". ;-))

Okay, geb ich Dir Recht, aber in welche Zellen gibst Du diese Formel ein?

Damit Du alle Daten erwischt müsstest Du eine Array-Formel über die ganze Spalte erstellen. Nimmst Du nur 100 Zeilen fehlt der 101te... (sag nix, ich weiß das macht jeder, nur um der Recherche beiträglich zu sein.)

Und bei mehreren Dateien dann logischer Weise für jede Datei eine Spalte.

Und ich wette die Daten sollen bestimmt alle untereinander. :-) Also brauchst Du noch min. eine Spalte was das zusammenkittet. (Keine Ahnung wie das gehen soll, Du wirst es wissen, trau ich Dir zu.)

Wenn ich die Formel als Array in eine Spalte donner und speicher, dann kriege ich... 8,13 Mb Dateigröße. Lutz hat 5 Dateien, ich bin man gnädig und sage er braucht nur 20 Spalten, dann bekommen wir (5+1)*8,13*20 eine satte Dateigröße von knapp 1 Gb.

Also ich weiß aus Erfahrung wie lange eine "paar-hundert-Mb" große Datei zum Öffnen braucht...

Mag ja jetzt alles gesponnen sein, aber selbst wenn den Bereich reduziert bläht sich die Datei auf und auf und auf. Und dann passiert der erste Fehler bei der Formelanpassung, Excel stürtzt ab, Datei kaputt usw. usw. Lesen wir alles alle Nase lang in den Foren.

Ein Makro hat für diesen Fall, naja, 50-100 Zeilen und die Datei bleibt klitze-klein und man braucht nie wieder irgendwas anpassen, es geht. Sogar wenn man Dateien aus einem Ordner entfernt oder hinzufügt. Wollt ich nur mal loswerden.

Aber Fakt ist auch wenn viele "Makro" hören, dann sagt Ihr Gehirn "Virenalarm" und wollen das einfach nicht. Kurioser Weise vertrauen Sie manchmal aber dann doch auf AddIns, als ob da drin kein Code werkeln würde. Komische Welt... ich glaub ich krieg 'ne Grippe.

Andreas.

Schon wieder was vergessen, gibt es doch alles schon:

http://www.rondebruin.nl/win/s3/win008.htm

Gute Nacht, Andreas.

Hallo Lutz, hallo Andreas,

Soweit mir bekannt gibt es keine formelbasierende Lösung die den Umfang von verschieblichen Datenbereichen, die sich auch noch in der Größe ändern können, abdeckt.

das geht so, wie bei einem dynamischen Bereichsnamen mit BEREICH.VERSCHIEBEN und dann INDEX herum gerankt und die gesuchten Spalten und Zeilen mit VERGLEICH ermitteln:

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

Diese Formel umfasst den kompletten Tabellenbereich, wenn die Tabelle in A1 beginnt.

Für mein vorheriges Beispiel würde dann der erste Wert unterhalb der Überschrift für den Vornamen ausgelesen mit:

=INDEX(BEREICH.VERSCHIEBEN($A$1;;;ANZAHL2($A:$A);ANZAHL2($1:$1));ZEILE(A2);VERGLEICH("Vorname";$A$1:$F$1;0))

Man kann die BEREICH.VERSCHIEBEN-Formel auch nehmen, um in jeder Datei die Tabelle zu benamen und dann diesen Namen in der INDEX-Formel nutzen.

Man könnte anstatt BEREICH.VERSCHIEBEN auch die Daten als Tabelle formatieren und dann den Tabellennamen benutzen.

Claus

Hallo Lutz,

dass der Wert verändert wurde, weiß die Zielmappe nur, wenn der Zellbezug noch stimmt. Wenn durch Einfügen oder Löschen von Spalten oder Zeilen dieser verloren ging, bzw. noch auf die Vorgängerzelle verweist, ist auch der Inhalt weg bzw. falsch.

Mein Beispiel war auch nur für das Einfügen oder Löschen von Spalten gedacht. Die Zeile kannst du auch mit VERGLEICH ermitteln, wenn du etwas hast, woran du das festmachen kannst (ähnlich der Überschriften in den Spalten).

Wenn du einer Spalte einen Namen vergibst, musst du diesen Bereich mit INDEX ansprechen. Ich habe der Spalte mit den Vornamen den Namen "Vorname" vergeben. Dann würde der erste Wert unterhalb der Überschrift mit:

=INDEX(Geburtstagliste.xlsx!Vorname;ZEILE(A2))

ausgelesen werden.

Die Formel gilt, wenn beide Mappen offen sind. Sonst ist der Pfad noch einzubauen.

Claus

Etwas ähnliches habe ich bereits versucht, indem ich den Quell-Spalten Namen gegeben habe, und die in meiner Formel verwendet habe, aber das habe ich ehrlich gesagt nicht ans Laufen bekommen ;(

Ich bin wohl ein wenig am Thema vorbei geschrappt, Entschuldigung.

Weiß auch nicht was mit mir ist, irgendwo bei BUG habe ich wohl abgeschaltet... vielleicht kriege ich eine Grippe.

Der Artikel dazu ist hier:

https://support.office.com/en-ie/article/Create-an-external-reference-link-to-a-cell-range-in-another-workbook-c98d1803-dd75-4668-ac6a-d7cca2a9b95f

Du musst Die Namen für den ganzen Datenbereich vergeben und in der Zieldatei eine Matrix-Formel verwenden.

Was Dich zum nächsten Problem bringt: Wenn sich der Datenbereich in A vergrößert und in der Zieldatei die Matrixformeln in zu wenigen Zellen steht, dann fehlen Daten.

Und an dem Punkt kommen ganz viele Leute ins Forum und fragen wie man das macht: Mit einem Makro.

Bei festen Datenbereichen machen das die meisten alleine, oft wird dabei zur Dateisuche meine FileSearch-Klasse benutzt:

https://dl.dropboxusercontent.com/u/35239054/FileSearch.cls

Eine elegante Lösung bei sich verschieblichen Daten ist die Überschriften zu nutzen. So kann ich in der Zieldatei alle Überschriften abklappern, danach in der Quelldatei suchen, den darunter liegenden Datenbereich ermitteln und die Werte in die Zieldatei kopieren. Das ist natürlich ein wenig komplizierter und naturgemäß Sonderbedingungen unterworfen. Und dann kommen viele zu mir und kaufen sich eins. .-)

Soweit mir bekannt gibt es keine formelbasierende Lösung die den Umfang von verschieblichen Datenbereichen, die sich auch noch in der Größe ändern können, abdeckt.

Aus einer externen Datei... puh, entweder Claus weiß das oder nicht. :-) Da geb ich den Ball ab.

Aber bei mehreren Dateien, never ever.

Andreas.