Microsoft Excel

Herbers Excel/VBA-Archiv

letzte Zeile in Tabellenblatt übertragen


Betrifft: letzte Zeile in Tabellenblatt übertragen von: Erik
Geschrieben am: 17.09.2019 19:19:04

Hallo liebe Experten*innen,

ich war beeindruckt von den komplexen und individuellen Lösungen, die auf konkrete Probleme hier angeboten wurden/werden. Mein Problem ist auch sehr konkret und eine Lösung, die meiner nahekommt habe ich vergeblich gesucht.

Mein Problem ist oft beim Mitlesen, dass ich gar nicht weiß, was Makros eigentlich sind und wie sie funktionieren, geschweige denn wo ich sie (richtigerweise) eingebe. Bitte das also bei einem Lösungsvorschlag berücksichtigen und ggf. Basics verlinken oder erklären.

Jetzt zu meinem Problem:

Ich möchte Prozesse in einem Arbeitsteam sinnvoll dokumentieren.

Meine Arbeitsmappe besteht aus mehreren Tabellenblättern. Die erste heißt Zusammenfassung und alle anderen (bis jetzt 9 an der Zahl) haben Namen wie „Waldwoche“ oder „Raumkonzept“. Alle Tabellenblätter haben dasselbe Format (bis auf die Zusammenfassung, später dazu). Zeile 1 ist die Überschrift über alle Spalten (zusammengefügt), dann folgen in Zeile 2 die Überschriften der Spalten. Von links nach rechts stehen da: Datum, Anlass, Protokoll, Beschlüsse, Erläuterungen, Reflexion und Protokoll. Bei Protokoll verlinke ich immer das .doc- Protokoll, ansonsten werden das Datum und Text eingegeben. Der erste Eintrag in diesem Schema steht also in Zeile 3. Die Tabelle füllt sich fortlaufend. Möglicherweise kommen noch weitere Themen, also Tabellenblätter, dazu.
Im Tabellenblatt Zusammenfassung ist der Aufbau identisch bis auf die erste Spalte. Dort steht das Thema und man wird beim draufklicken gleich in das entsprechende Tabellenblatt verlinkt. Das alles habe ich noch ein bisschen mit Farben verschönert, überflüssige Spalten ausgeblendet und bin ganz zufrieden damit.

Jetzt kommt der Teil, den ich gerne hätte, aber selber nicht mehr umsetzen kann:
Immer die letzte Zeile eines jeden Tabellenblattes soll automatisch in die dazugehörige Zeile in der Zusammenfassung übertragen werden (ist ja auch derselbe Aufbau). Das soll auch funktionieren, wenn zwischendrin leere Felder sind (wenn es bspw. kein Protokoll gab oder keine Erläuterungen notwenidg sind). Der Aufbau der Zusammenfassung und der anderen Tabellenblättern ist ja wie gesagt bis auf die erste Spalte exakt dieselbe.

Auf Wunsch liefere ich gerne noch Screenshots dazu und/oder beantworte Fragen dazu.
Vielen Dank für jedwede Mühe im Voraus.

Mit freundlichen Grüßen
Erik

  

Betrifft: AW: letzte Zeile in Tabellenblatt übertragen von: 1713541.html
Geschrieben am: 17.09.2019 19:54:01

Hi Erik,
mit Screenshots kann man meistens nicht soviel anfangen, also bitte eine Beispielmappe hochladen. Es reicht eine mit 2 Blättern: Zusammenfassung und einem Projektblatt, in dem (filtive) Daten stehen.

Außerdem wäre noch interssant, wann das ganze passieren soll, wenn sich etwas ändert (wo?), beim Klick auf ein Button ...

Evtl lässt sich auch ohne VBA eine Formlelösung mit einem Sverweis bauen, aber das kann man nur sagen, wenn man die Mappe sieht.

Gruß
Regina

  

Betrifft: AW: letzte Zeile in Tabellenblatt übertragen von: 1713543.html
Geschrieben am: 17.09.2019 20:37:51

Liebe Regina,

hier die Datei:
https://www.herber.de/bbs/user/132079.xlsx
(Bekomme ich den Eingangsthread geändert, um den upload noch einzufügen?)

Zu deiner Frage:
Am liebsten wäre mir natürlich, es würde sich immer gleich dann ändern, wenn man in einem Blatt eine neue Zeile anfängt und etwas einträgt, sodass die Zusammenfassung demzufolge immer autmoatisch/automatisiert auf dem neuesten Stand ist.
Z.B. Wenn ich ein Datum und einen Anlass bei einem Thema eintrage und es sofort in die Zusammenfassung mit nachfolgenden leeren Zellen übertragen wird, sodass diese leeren Felder immmer gleich "ermuntern" den Rest einzutragen.

LG

  

Betrifft: AW: letzte Zeile in Tabellenblatt übertragen von: 1713545.html
Geschrieben am: 17.09.2019 20:59:10

Hi, ich habe mir die Datei eben angesehen. Eine reine Formellösung fällt mir dazu nicht ein, eher ein kleiner VBA-Code.
Am einfachsten wäre es, wenn man auf dem Blatt "Zusammenfassung" ein Button anlegt, dass die Zusammenfassung aktualisiert, sich also von allen Blättern die letzte Zeile holt. Dabei könnte man dann auch gleich dafür sorgen, dass neu angelegte Blätter in der Zusammenfassung automatisch erscheinen.
Eine Alternative, um bei Änderungen in den Blättern automatisch zu reagieren ist recht aufwändig.

Gruß
Regina

  

Betrifft: AW: letzte Zeile in Tabellenblatt übertragen von: 1713546.html
Geschrieben am: 17.09.2019 21:11:42

Dann würde mir die Lösung mit dem Button auf jeden Fall reichen. Ansonsten klingt das sehr gut und ich bin schon ganz hippelig :)

LG

  

Betrifft: AW: letzte Zeile in Tabellenblatt übertragen von: 1713547.html
Geschrieben am: 17.09.2019 21:16:31

...ok, ich bastel mal
VG regina

  

Betrifft: AW: letzte Zeile in Tabellenblatt übertragen von: 1713556.html
Geschrieben am: 17.09.2019 21:45:38

Hi, dann schau Dir das mal an, ob das passt. Ich habe Dir in das Blatt Zusammenfassung ein Schaltfläche "Aktualisieren" eingebaut.

Hinweis 1: Der Text in Spalte "A" von Zusammenfassung muss immer identisch sein mit dem Text in A1 des jeweiligen Blattes. bei neuen Blättern macht das der Makro automatischm baut auch den Link ein. Da die Liste jetzt komplett leer ist, werden alle Einträge neu erstellt.

Hinweis 2: Auf den Einzelblättern wird immer der letzte Eintrag berücksichtigt, bei dem in Spalte A ein Datum steht.

Noch ein Hinweis: Auch wenn Du Zeilen und Spalten ausblendest, solltest Du Hintergrundfarben und Rahmen ne über ganze Zeilen und Spalten legen. Das bläht die Datei unnötig auf.

Dann frohes Testen. bei Frage melde Dich gerne.

Gruß
Regina

https://www.herber.de/bbs/user/132081.xlsm

  

Betrifft: AW: letzte Zeile in Tabellenblatt übertragen von: 1713563.html
Geschrieben am: 17.09.2019 22:11:16

Hallo Regina,

wow, das ging schnell. Wenn ich allerdings auf "Aktualisieren" klicke, kommt die Meldung, das Makro sei nicht verfügbar.

LG

  

Betrifft: Du musst schon "Makros aktivieren"...oT von: 1713565.html
Geschrieben am: 17.09.2019 22:13:19

...

  

Betrifft: AW: letzte Zeile in Tabellenblatt übertragen von: 1713566.html
Geschrieben am: 17.09.2019 22:13:25

hast Du nach dem Öffnen der Datei die Makros aktiviert? Oben in der gelben Zeile unter dem Menüband?
Ggf. noch mal schließen und nach dem Öffnen aktivieren.
Regina

  

Betrifft: Formellösung von: 1713555.html
Geschrieben am: 17.09.2019 21:41:22

Hi,

in Zusammenfassung!B2:

=VERWEIS(2;1/(INDIREKT("'"&$A2&"'!A1:A1000")<>"");INDIREKT("'"&$A2&"'!Z1S(-1):Z1000S(-1)";))

und nach unten und nach rechts kopieren.

AAAAABER: Die Blattbezeichungen in Spalte A müssen exakt mit den realen Blattnamen übereinstimmen - also nicht "Beabachtung und Dokumentation" sondern "Beob. u. Dok."

Falls Du das nicht magst, nimm eine ausgeblendete Hilfsspalte, in der die exakten Blattnamen stehen und nimm in der Formel auf eben diese Hilfsspalte Bezug.

Zudem bin ich davon ausgegangen, dass ein Datensatz - egal auf welchem Blatt - immer ein Datum enthält. Anhand des letzten Datums wird in der Formel die gesuchte Zeile ermittelt.

VG, Boris

  

Betrifft: AW: Formellösung von: 1713557.html
Geschrieben am: 17.09.2019 21:49:06

Hallo Boris,

gesagt, getan, aber dann steht in B2 nur die errechnete Zahl 43690.

Ich habe Regina die .xlc geschickt (als Link), vielleicht hilft das.

Ansonsten ist mir aufgefallen, dass ich, wenn es denn ginge, die von Regina erwähnte Erweiterungsfunktion sehr gut fände (es akutalisiert ggf. neue Blätter) und schön wäre auch, wenn die Formatierung bestehen bleiben würde.

Ich denke, der Anblick der Arbeitsmappe klärt viele Fragen.

LG und vielen Dank für deine Mühe

  

Betrifft: 46390... von: 1713558.html
Geschrieben am: 17.09.2019 21:51:54

Hi,

...als Datum (TT.MM.JJ) formatiert, ist der 13.08.19 - und das ist doch genau der Eintrag, den Du haben möchtest. Oder was habe ich hier nicht verstanden?

VG, Boris

  

Betrifft: 43690 meinte ich natürlich ...oT von: 1713559.html
Geschrieben am: 17.09.2019 21:52:29

...

  

Betrifft: AW: 43690 meinte ich natürlich ...oT von: 1713560.html
Geschrieben am: 17.09.2019 22:03:12

Ok, nach dem Ändern der Kategorie funkioniert es. Wow.

Lässt sich der Charakter des Hyperlinks in den Spalten "Protokoll" erhalten (sodass ich ihn nur anklicke und zum Serverinternen Protkoll komme)?


LG und danke für die schnelle Umsetzung.

  

Betrifft: Siehe meine Antwort hier... von: 1713562.html
Geschrieben am: 17.09.2019 22:04:34

Hi,

...mit Beispieldatei: https://www.herber.de/forum/messages/1713561.html

VG, Boris

  

Betrifft: Beispieldatei... von: 1713561.html
Geschrieben am: 17.09.2019 22:03:37

Hi,

...und Du kannst auch weitere Blätter hinzufügen (musst dazu natürlich weitere Zeilen einblenden).

https://www.herber.de/bbs/user/132082.xlsm

xlsm nur deshalb, weil ich auf eine alte Excel4-Macrofunktion (ARBEITSMAPPE.ZUORDNEN) zurückgegriffen habe zwecks dynamischer Auflistung der Tabellenblätter in der ausgeblendeten Spalte A.

VG, Boris

  

Betrifft: AW: Beispieldatei... von: 1713567.html
Geschrieben am: 17.09.2019 22:34:35

Wow, habe jetzt mal alles angepasst, die Protokolle aus der Zusammenfassung rausgeschmissen (nicht zwingend erforderlich) und bin jetzt sehr zufrieden. Habe es sogar geschafft ein neues Blatt einzufügen und alles funzt tadellos.

Vielen lieben Dank (auch an Regina).

Nur eine abschließende Frage:
Mein Laptop ist mitnichten Leistungsschwach. Dennoch hat Excel ganz schön was zu tun, als ich bspw. an den Formatierungen gearbeitet habe. Woran liegt das? Verschlimmert sich das, je voller die Tabellen werden?

Liebe, dankbare Grüße

  

Betrifft: AW: Beispieldatei... von: 1713569.html
Geschrieben am: 18.09.2019 06:16:42

Hallo Eric,
nur so interessehalber: Funktioniert meine Variante? Wäre nett, wenn Du da auch eine Rückmeldung geben würdest.

Was die Geschwindigkeit anbelangt: Hast Du meinen Rat befolgt und alle Formatierungen (auf allen Blättern) die sämtliche Spalten und Zeilen betreffen (vor allem Hintergrundfarben und Rahmen) rausgeschmissen?
ich kann außerdem nicht beurteilen, was die Funktion "frisst", runterkopiert bis Zeile 1000 könnte die auch bei jder Neuberechnung ganz schön bremsen. Aber das weiß Boris sicher besser.

Gruß Regina

  

Betrifft: Performance von: 1713584.html
Geschrieben am: 18.09.2019 08:41:06

Hi,

Regina hat Recht wenn sie die Performance bei INDIREKT anspricht, da die Funktion eben volatil ist.
Bis Zeile 1000 war jetzt auch sehr großzügig - grenz den Bereich auf die gerade benötigte Größe ein.
Und Formatierungen können auch ganz schön bremsen - auch hier darauf achten, dass nur der notwendige Bereich formatiert ist.

VG, Boris

  

Betrifft: Performance - Lösung von: 1713604.html
Geschrieben am: 18.09.2019 10:11:22

Hi
volatile Formeln sind immer dann kritisch, wenn sie zusätzlich auch noch zeitaufwendige Berechnungen enthalten und in großer Anzahl auftreten.

ich würde hier empfehlen, die Berechnung der letzten benutzen Zeilennummer auf dem jeweilgen Tabellenblatt selbst in einer immer gleichen Zelle der Kopfzeile durchzuführen (z.B. H1).

dann reduziert sich der Inhalt der volatilen Formeln auf zwei einfache Zellabfragen in dem Stil:

=Index(INDIREKT("'"&$A3&"'!A:G");INDIREKT("'"&$A3&"'!H1");Spalte(A1))

damit sind die volatilen Funktionen sehr schnell und stören die Performance nicht.
die Berechnung der letzten Zeilennummer auf jedem Blatt steht dann in normalen nichtvolatilen Formeln und wird daher nur bei Bedarf neu berechnet.

Gruß Daniel
  

Betrifft: AW: Performance - Lösung von: 1713932.html
Geschrieben am: 19.09.2019 15:20:35

Hallo Boris, Daniel, hallo Regina,

tolles Forum hier, wirklich. Schnell und äußerst kompetent.

Erstmal zu Reginas Frage: Ich habe mir erst beide Lösungen angeschaut. Als ich gerade dabei war, dass mit den Makros zu prüfen, kam Boris Alternative und die habe ich auch ausprobiert und aufgrund des fehlenden Buttons als besser befunden. Mich plagt das schlechte Gewissen, weil du auch eine super Lösung gefunden hast und ich dich nicht damit enttäuschen wollte, dann einfach eine andere vorzuziehen. Bitte nimm es nicht persönlich. Deinen Rat mit den Formatierungen habe ich befolgt und dazu unten mehr.


Ich habe tatsächlich die ganze Tabelle neu gemacht und darauf geachtet, nur den relevanten Bereich zu formatieren. Warum auch immer hatte das große Wirkung, denn nun dauert auch das Öffnen der Datei nicht mehr 6 bis 7 Sekunden, sondern ist sofort da. Ansonsten scheint auch jetzt die Performance zu stimmen. (Da stellt sich für mich die Frage, ob die durchgehende Formatierung von Farben, Rahmen etc. auf alle Zeilen und Spalten wirklich zu solchen Einbrüchen führen kann...)

Zum Lösungsvorschlag von Daniel:
Ich verstehe es nicht. Tut mir leid. Ich verstehe weder, wie das zu besserer Performance führt noch wie ich deine Idee umsetzen kann. Da fehlen mir offensichtlichen die Kenntnisse für die Zusammenhänge und die Formeln. Vielleicht könntest du es nochmal "einfacher" und praktischer erklären.

Eine winzige Sache könnte noch zum (überflüüsigen) Perfektionismus beitragen: Wenn in der Zusammenfassung die "0" bei leeren Zellen einer tatsächlich leeren Zelle weichen würde. Aber das ist mitnichten eine Beanstandung sondern eher eine Macke meinerseits...


Ich habe die fertige Mappe nochmal hochgeladen. Nachdem vermutlich jetzt klar ist, welchen Zweck die Tabelle hat, bin ich natürlich auch noch offen dafür, was möglicherweise noch besser sein könnte und von mir nicht bedacht worden ist. Ansonsten ist das bis jetzt so, wie ich es mir vorgestellt und gewünscht hatte und ich freue ich mich darauf, in einem Jahr (oder so) hier mal zu reflektieren, wie das Füllen der Listen so von der Hand gegangen ist und ich meine Teamleiterrolle dadurch optimieren konnte.

https://www.herber.de/bbs/user/132122.xlsx



Herzliche, Dank erfüllte Grüße
Erik

  

Betrifft: AW: Performance - Lösung von: 1713953.html
Geschrieben am: 19.09.2019 18:05:15

Hallo Erik,
kein Problem. Aber danke für die Rückmeldung.
Wie ich schon geschrieben hatte, bremsen unnötige Formatierungen jede Datei aus. Das gilt insbesondere auch für Bedingte Formatierungen und Datenüberprüfungen.

Gruß
Regina

  

Betrifft: AW: Performance - Lösung mit Bsp-Datei von: 1714097.html
Geschrieben am: 20.09.2019 14:21:16

Ok, dann für dich eine meiner Beschreibung entsprechend umgestaltete performanceorientierte Beispieldatei.
Beachte auch, dass ich eine etwas optimierte Methode zur Berechnung des anzuzeigenden Inhalts verwende, nämlich das neuste Datum.
Das hat dann zur folge, dass du in Spalte A der einzelnen protokollblätter immer auch ein echtes Datum eintragen musst (siehe Brandschutz).
Sollte das für dich nicht praktikabel sein und unabhängig vom Datum die letzte Zeilennummer auf dem Blatt verwendet werden soll, dann kannst du auch auf jedem Blatt die entsprechende Formel zum Berechnen der letzten Zeilennummer in die Zelle G1 einfügen.
https://www.herber.de/bbs/user/132133.xlsx
Gruß Daniel

Beiträge aus dem Excel-Forum zum Thema "letzte Zeile in Tabellenblatt übertragen"