ich möchte eine Art Vereinsliste erstellen, in der ich auf dem ersten Blatt die Namen und Adressen und auf dem zweiten die Anwesenheit führe. Die Namen auf der Anwesenheitsliste sollen automatisch mit der Adressliste synchron gehalten werden.
Ich habe also die Spalten „Vorname“ und „Nachname“ markiert und sie mit der Name-Funktion von Excel „Namen“ genannt und siehe da - die Namen werden auch in der Anwesenheitsliste auf dem aktuellen Stand gehalten.
Mein Problem ist jetzt, dass z.B. beim Einfügen einer Zeile in der Adressenliste auch in der Anwesenheitsliste eine Zeile eingefügt wird, aber nur in dem verknüpften Bereich. Dadurch stimmen aber die Informationen, wer wann anwesend war, nicht mehr, denn dort wurde ja keine Zeile eingefügt. Ich könnte natürlich die entsprechenden Zellen manuell verschieben, aber da ist die Gefahr groß, wenn man das mal vergisst, ein großes Chaos zu produzieren. Außerdem bin ich überzeugt, dass Excel eine bessere Lösung parat hat.
Ich muss dazu sagen, dass ich „Excel-Frischling“ bin und deshalb mit Makros und komplexeren Formeln nicht sonderlich viel anfangen kann.
Falls also jemand eine unkomplizierte Lösung weiß, wäre ich dafür sehr dankbar!
Verknüpfen von Zellen über mehrere Blätter
Hallo Joatpdm,
ich möchte eine Art Vereinsliste erstellen, in der ich auf dem
ersten Blatt die Namen und Adressen und auf dem zweiten die
Anwesenheit führe. Die Namen auf der Anwesenheitsliste sollen
automatisch mit der Adressliste synchron gehalten werden.
Ich habe also die Spalten „Vorname“ und „Nachname“ markiert
und sie mit der Name-Funktion von Excel „Namen“ genannt und
siehe da - die Namen werden auch in der Anwesenheitsliste auf
dem aktuellen Stand gehalten.
Die Adressliste sollte auch eine Spalte Mitgliedsnummer haben, möglichst die Spalte A. Falls es schon eine Mitgliedsnummer gibt aber nicht in Spalte A, dann verschieb sie nach Spalte A, Falls noch nicht dann füge links von Spalte A eine Leerspalte ein. Hier trägst dann als provisorische Mitgliedsnummer eine fortlaufende Nummer für alle Mitglieder ein - es dürfen keine Nummern doppelt vorkommen!.
Auf der Anwesenheitsliste fügst du links von Spalte A eine Leerspalte ein. In diese kopierst du die Mitglieds-Nummern. Jetzt kannst du Daten für Name, Vorname etc. mit der Funktion SVERWEIS aus der Adressliste in andere Spalten der Anwesenheits übernehmen.
Wenn jetzt neue Mitglieder aufgenommen werden/oder den Verein verlassen muss du „nur“ deren Mitgliedsnummer in der Anwesenheitsliste ergänzen/löschen und ggf. die Formeln mit den SVERWEISEN kopieren.
Leider ist dann mein Problem immer noch da. Vielleicht war es etwas umständlich formuliert, deshalb habe ich mal eine Beispiel-Datei erstellt, die vom Aufbau her meiner Datei entspricht (Namen stimmen natürlich nicht überein )
Dort habe ich bei der Anwesenheit schon einige Eintragungen gemacht. Wenn jetzt ein Mitglied dazu kommt, würde ich auf dem Adressblatt eine Zeile einfügen. Wenn ich das allerdings z.B. über „G. Westerwelle“ tue, dann „rutscht“ er zwar auch auf der Anwesenheitsliste runter und macht Platz für den Nächsten, aber seine +/- bleiben ja in der vorhergehenden Zeile und alle Daten verschieben sich (weil sie an ihrer Stelle bleiben).
Jetzt könnte ich natürlich die Liste einfach nach unten fortsetzen, aber die Gruppenstruktur muss so erhalten bleiben.
Gibt es denn eine Möglichkeit, im Anwesenheitsblatt den Inhalt der Spalten E ff. mit dem der Spalten C und D zu koppeln, sodass, wenn C und D verschoben werden, E ff. „mitwandern“?
Puh, wieder viel Text, ich hoffe, das versteht jemand??? Vielleicht wird es ja aus der Datei ersichtlich…
durch die Umsetzung der von Franz vorgeschlagenen Eintragung der Mitgliedsnummern und die entsprechenden Formeln in beiden Tabellen ist das meiste schon geschafft.
Jetzt nur noch in der Tabelle in der Spalte Name folgende Formel einfügen (gilt für Zeile 4):
=WENN(A4"";SVERWEIS(A4;Adressen!$A$4:blush:D$1003;3;FALSCH);"")
In der Spalte Vorname diesselbe Formel, nur die letzte 3 durch 4 ersetzen.
Diese beiden Formeln nach unten kopieren.
Wenn jetzt in der Adresstabelle ein neuer Eintrag gemacht wird, dann wird dieser automatisch - inklusive Mitgliedsnummer! - in der Tabelle aktualisiert.
Bei dieser Umsetzung haben die Mitgliedsnummer allerdings keine Aussagekraft, da die Personen bei jeder neu eingefügten Zeile ein Teil der Personen eine andere Mitgliedsnummer bekommt. Wenn die Mitgliedsnummer egal ist, dann kann das so bleiben. Dann würde ich sie aber nicht Mitgliedsnummer, sonder lieber ID etc. nennen. Das ist aber rein formal.
Vielen Dank erstmal bis hierher!
Das Aktualisieren der Inhalte klappt gut, aber die Daten der Anwesenheitsliste (also ich meine die Information, wann wer da war) stimmen ja in dem Moment nicht mehr, wenn ich ein neues Mitglied einfüge! Oder habe ich etwas übersehen? Bei mir rutschen nach wie vor alle Mitglieder unterhalb des neu eingefügten einen Platz nach unten, wodurch die Informationen in den Zellen rechts daneben ja nicht mehr zu den Namen passen, was ich natürlich vermeiden will.
Theoretisch müsste das ja lösbar sein, indem ich die Blätter verknüpfe (also mit STRG + Klick), wenn ich eine Zeile einfüge. Die Probleme dabei sind:
1.Das Verknüpfen sollte automatisch geschehen und nur in dem Moment, wo ich eine Zeile einfüge.
2.Die Formeln der umgebenden Zeilen müssten in den verknüpften Blättern auf die neu eingefügte Zeile übertragen werden (sonst holt sie sich ja nicht mehr die Daten aus der Adressliste und es bleibt eine leere Zeile).
Praktisch kann ich das nur leider nicht umsetzen, weil das meine momentanen Excel-Kenntnisse etwas übersteigt
Upps, da habe ich mich zu sehr auf die Namen konzentriert.
Das Problem ist genau genommen die Abbildung der Verknüpfung von 2 Tabellen einer Datenbank, wie es z. B. mit Access einfach zu lösen ist.
Mit Excel scheint es mir nur zu gehen, wenn die Mitgliedsnummer einer Person eindeutig ist und dieser immer zugeordnet bleibt.
Das bedeutet, dass die Mitgliedsnummer in der Anwesenheitstabelle fix herübergeolt werden muss, z. B. mit
=WENN(Adressen!$B4 „“;Adressen!$B4;"")
Die Formeln für Name und Vorname können bleiben wie beschrieben
=WENN(A4"";SVERWEIS(A4;Adressen!$A$4:blush:D$1004;3;FALSCH);"")
Das Einfügen neuer Zeilen muss dann tatsächlich in beiden Tabellen gleichzeitg erfolgen (beide markieren!).
Nachteil:
1.
In der Anwesenheitstabelle müssen in der neu eingefügten Zeile die Formeln nachgezogen (runterkopiert) werden.
Die Mitgliedsnummer muss in der Adresstabelle von Hand eindeutig gehalten werden. Das ginge z. B. einfach so, dass anhand der Gruppen nummeriert wird: S-1, S-2, S-3 … A-1, A-2 … usw.
oder einfach Zahlen mit
=MAX($A2:blush:A1000)+1 in Zelle A1 der Adresstabelle zum Anzeigen der nächsten Nummer
So sollte es fürs Erste mal funktionieren, leider noch mit relativ viel Handarbeit beim Einfügen neuer Personen. Vielleicht weiß aber jemand noch eine einfacher, automatischere Lösung…
erste Versuche mit einem Makro…
Das ist natürlich alles etwas umständlich…
Ich experimentiere aber gerade etwas mit Makros herum und denke, dass ich mir so einige Abläufe automatisieren könnte. Wenn z.B. auf allen Blättern eine Zeile ausgeschnitten und an anderer Stelle (die mit der InputBox per Mausklick festgelegt wird) eingefügt werden soll, habe ich mir folgenden Code gebastelt:
Dim rngBereich As Range
ActiveCell.Rows(„1:1“).EntireRow.Select
Sheets(Array(„Adressen“, „Anwesenheit“)).Select
Sheets(„Adressen“).Activate
Selection.Cut
Set rngBereich = Application.InputBox(„Wo soll eingefügt werden?“, „Zelle wählen“, Type:=8)
rngBereich.Rows(„1:1“).EntireRow.Select
ActiveSheet.Paste
Leider bekomme ich immer einen Laufzeitfehler 1004 („Die Paste-Methode des Worksheet-Objektes konnte nicht ausgeführt werden“), im VBA-Editor wird die Zeile ActiveSheet.Paste gelb markiert.
Weißt du (oder jemand anderes) vielleicht Rat, wie ich den Code verändern könnte, damit er funktioniert? Er ist entlehnt aus einem per Makrorecorder aufgezeichneten „normalen“ Cut-Paste Vorgang, den ich nur nach meinen Bedürfnissen erweitert habe.
Danke!!!
Ich experimentiere aber gerade etwas mit Makros herum und
denke, dass ich mir so einige Abläufe automatisieren könnte.
Wenn z.B. auf allen Blättern eine Zeile ausgeschnitten und an
anderer Stelle (die mit der InputBox per Mausklick festgelegt
wird) eingefügt werden soll, habe ich mir folgenden Code
gebastelt:
Hallo Johannes,
Sub tt()
Dim rngBereich As Range, rngQuelle As Range
Worksheets("Tabelle1").Activate
Set rngQuelle = ActiveCell.EntireRow
Worksheets("Tabelle2").Activate
Set rngBereich = Application.InputBox("Wo soll eingefügt werden?", "Zelle wählen", Type:=8)
rngQuelle.Cut Destination:=rngBereich.EntireRow.Cells(1, 1)
End Sub
Set rngTarget = Application.InputBox(„Wo soll eingefügt werden?“, „Zelle wählen“, Type:=8).Rows(„1:1“).EntireRow
rngSource.Cut rngTarget
Set rngDelete = ActiveCell.Rows(„1:1“).EntireRow
rngDelete.Delete Shift:=xlUp
Set rngDelete = Nothing
Set rngSource = Nothing
Set rngTarget = Nothing
Sheets(„Adressen“).Select
End Sub
Das Problem ist nun, dass sich die Variablen immer nur auf ein Tabellenblatt zu beziehen scheinen, obwohl doch „Adressen“ und „Anwesenheit“ verknüpft wurden. Wie kann ich denn erreichen, dass die betreffenden Zeilen auf beiden Blättern bearbeitet werden?
vielen Dank, das klappt soweit. Habe das Makro noch ein
bisschen bearbeitet, dass es wie folgt aussieht:
Hallo Johannes,
ich hb doch grad das "Rows(„1:1“) rausgeworfen weil man es nicht braucht.
Und Select nehme ich nur wenn ich es unbedingt muß, sonst vermeide ich es strikt.
Das Problem ist nun, dass sich die Variablen immer nur auf ein
Tabellenblatt zu beziehen scheinen, obwohl doch „Adressen“ und
„Anwesenheit“ verknüpft wurden. Wie kann ich denn erreichen,
dass die betreffenden Zeilen auf beiden Blättern bearbeitet
werden?
Beschreib mal bitte an einem konkreten Beispiel genauer was da wo und wie „bearbeitet“ werden soll.
Und vermeide bitte spezielle Blattnamen, nimm Worksheets(1) o.ä.
ich hb doch grad das "Rows(„1:1“) rausgeworfen weil man es
nicht braucht.
Und Select nehme ich nur wenn ich es unbedingt muß, sonst
vermeide ich es strikt.
Hallo Reinhard,
das überflüssige Zeug ist wahrscheinlich beim Arbeiten mit dem Recorder wieder rein gekommen, danke für den Hinweis. Wie gesagt, ich bin kompletter Anfänger was Makros angeht und muss mich erstmal reinfinden…
Beschreib mal bitte an einem konkreten Beispiel genauer was da
wo und wie „bearbeitet“ werden soll.
Und vermeide bitte spezielle Blattnamen, nimm Worksheets(1)
Im Speziellen soll es folgendermaßen funktionieren:
Sobald das Makro ausgelöst wird, wird die Zeile, in der sich die aktive Zelle befindet, komplett ausgeschnitten. Per Mausklick in eine beliebige Zelle einer anderen Zeile bestimmt der User, in welcher Zeile der Inhalt eingefügt werden soll. Im Anschluss soll die komplette Zeile, aus der die Informationen stammen, gelöscht werden, damit keine Leerzeile entsteht.
Im Grunde bin ich ja schon so weit (wenn auch wahrscheinlich mit etwas umständlichen Makros), nur soll das Ganze an gleicher Stelle auf Worksheet1 UND Worksheet2 passieren.
Das habe ich bisher noch nicht geschafft.
edit:
Nur, falls es nicht deutlich geworden ist:
Es ist nicht so gemeint, dass der Inhalt z.B. von Zeile6(Worksheet1) in Zeile15(Worksheet1) und Zeile15(Worksheet2) übernommen werden soll, sondern Zeile6(Worksheet1) in Zeile15(Worksheet1) und Zeile6(Worksheet2) in Zeile15(Worksheet2).
Wie
gesagt, ich bin kompletter Anfänger was Makros angeht und muss
mich erstmal reinfinden…
ich habe nicht vergessen wie ich mal mit Excel und später Excel-Vba anfing, mit Nullahnung.
Deshalb ist mir völlig klar warum du Select benutzt. Machen alle die anfangen, ich damals auch )
Okay, ich antworte in einigen Foren, meist sehr schnell, deshalb fallen da meine Antworten manchmal kurz aus, in der Art:
Warum schon wieder Select, laß das weg
Warum wieder Rows(), laß das weg.
Es ist gar kein Theam wenn dann der Anfrager nachfragt, warum. Dann erläutere ich oder jmd. anders ihm das.
Aber das gleich bei jeder meiner Antworten gleich ausführlich zu erklären, auch mit der Ungewissheit ob es der Anfrager überhaupt liest, gibt da leider so Eintagsfliegen , nö, das mache ich nicht.
Beschreib mal bitte an einem konkreten Beispiel genauer was da
wo und wie „bearbeitet“ werden soll.
Und vermeide bitte spezielle Blattnamen, nimm Worksheets(1)
Im Grunde bin ich ja schon so weit (wenn auch wahrscheinlich
mit etwas umständlichen Makros), nur soll das Ganze an
gleicher Stelle auf Worksheet1 UND Worksheet2 passieren.
Das habe ich bisher noch nicht geschafft.
Da liegt mein Verstehensproblem.
Ich frag mal so, du willst in Tab1 F6 markieren, dann ein Makro haben was sowohl in Tab1 und Tab2 die Zeile 6 in die Zeile verschiebt deren Zelle die du mit der Inputbox auswählst.
Ist das so korrekt?
ich habe nicht vergessen wie ich mal mit Excel und später
Excel-Vba anfing, mit Nullahnung.
Deshalb ist mir völlig klar warum du Select benutzt. Machen
alle die anfangen, ich damals auch )
Das beruhigt mich, dann scheint man es ja lernen zu können (und kommt nicht als VBA-Crack auf die Welt)…
Ich frag mal so, du willst in Tab1 F6 markieren, dann ein
Makro haben was sowohl in Tab1 und Tab2 die Zeile 6 in die
Zeile verschiebt deren Zelle die du mit der Inputbox
auswählst.
Genau so sollte es sein. Aber das macht der aktuelle Code nicht, obwohl ich darin ja am Anfang Tab1 und Tab2 verknüpfe (dachte ich zumindest).
Hast du (oder jemand anderes) inzwischen eine Lösung dafür? Ich habe inzwischen schon tausend Varianten probiert und verzweifle langsam. Es kann ja eigentlich nicht so schwer sein!
Bin dankbar für alle Tipps!
Viele Grüße,
)
, nö, das mache ich nicht.