gruppierte Adressverwaltung (Gruppen in anderen Blättern)

[boevent]

Hallo zusammen,

ich hoffe es kann mir jemand helfen. Ich suche bereits seit Tagen nach der richtigen Vorgehensweise. Meine Excel-Kenntnisse sind OK, jedoch habe ich keine Vorerfahrung mit Makros. Und entschuldigt bitte die Überschrift, aber mir wollte nix besseres einfallen!

Es geht um eine Adressverwaltung in einer Sportabteilung eines Vereins. Ich hoffe ich hab das Ganze auf das wesentliche beschränkt, indem ich meine Struktur stark abgeschwächt habe.

Ich möchte auf einem Tabellenblatt „Mitglieder“ alle Mitglieder verwalten. Zusätzlich soll auf diesem Tabellenblatt die Sparte der sie angehören notiert sein. Als Sparten gibt es „Herren“, „Damen“ und „Mixies“.
Beispieldatensatz wäre:
Name Vorname Sparte Sparte
Hans Schneider Männer
Mia Müller Damen Mixies

Ein Spieler kann mehreren Sparten zugeordnet sein (Wenn er z.B. bei den Herren und bei den Mixies spielt).
Alle Spieler die bei den Herren spielen, sollen jetzt auf dem neuen Tabellenblatt „Herren“ aufgelistet werden usw.
Es gibt also 4 Tabellenblätter: „Mitglieder“, „Herren“, „Damen“, „Mixies“.

Änderungen können lediglich auf dem Tabellenblatt „Mitglieder“ vorgenommen werden.

Ich möchte das wenn ich etwas bei Mitglieder ändere, die Liste auf den anderen Tabellenblättern angepasst wird.

Mein Lösungsansatz war bisher mit einem Spezialfilter. Das funktioniert auch ganz gut jedoch habe ich da das Problem mit der Aktualisierung, was ich trotz intensiver Suche nicht lösen konnte. Habe auch gelesen das das mit der 3.2. gefixed, aber weiter bin ich leider nicht gekommen. Autofilter ist nicht so ganz das ware, da ich ja das Problem habe, dass ein Spieler in mehrern Sparten sein kann.

Ich hoffe man kann mein problem überhaupt verstehen, ansonsten versuche ich es auch gerne noch mal präziser zu beschreiben.
Schon mal herzlichen Dank dafür!

LG
Thomas

[boevent]

Also habe mich da mal versucht einzulesen.
Allerdings wenn ich das richtig verstanden habe funktioniert dieser Lösungsansatz bei mir nicht, da ein Name mehreren Gruppen zugeordnet werden kann?!

Oder habe ich das falsch verstanden? Übere weitere Tipps bin ich natürlich sehr dankbar!

edit: ich könnte natürlich mit einem "Oder" noch die andere Spalte abfragen, Aber das Wäre dann eine seeeehr lange Formel...

[boevent]

Also erstmal danke an hiob für die Formel, auch wenn ich noch nicht ganz durchgestiegen bin!
Also habe mich jetzt mal etwas eingearbeitet in die MAtrixformel und die könnte tatsächlich die Lösung für mich sein. Allerdings bleiben noch Fragen.

Angepasste Ursprungsformel von hiob:

Code: Alles auswählen

=WENN(ZÄHLENWENN($H$3:$H$120;$A$123)>=ZEILE(A1);INDEX($A$3:A$120;KKLEINSTE(WENN($H$3:$H$120=$A$123;ZEILE($A$1:$A$120));ZEILE(A1)));"")

Ursprungscode zur Ermittlung der Zeilennummer:

Code: Alles auswählen

=KKLEINSTE(WENN($H$3:$H$120=$A$123;ZEILE($A$3:$A$120));ZEILE(A1))

Ich möchte jedoch das in Spalte "H" UND in Spalte "I" geprüft wird ob da das steht was in "A123" steht.
Folgende Änderung gibt mir jedoch immer "1" zurück!

Code: Alles auswählen

=KKLEINSTE(WENN(ODER($H$3:$H$120=$A$123;$I$3:$I$120=$A$123);ZEILE($A$1:$A$120));ZEILE(A1))

Oder kann ich das so einfach nicht machen?

Grundlegende Fragen:
Für was ist das Zeile(A1)? Dafür könnte ich doch genauso einfach eine "1" nehmen, oder? ist das dafür, dass es sich automatisch beim herunterziehen der Formel erhöht? Weil in die nächste Zeile muss ja dann A2... Aber das funktioniert irgendwie nicht!

Kann ich die Formel auch für einen ganzen Bereich übernehmen? Weil ich will ja die ganze Tabelle und nicht nur Spalte A mit den Namen. Also auch Straße, Geb. Datum etc.

Vielen Dank für die Hilfe!!!

[balu]

Hallo Thomas,

ausnahmsweise mal ohne viele Worte meinerseits, habe ich dir ne Beispieldatei angehängt. Vielleicht ist es ja das, was Du willst.

Nur zur kurz-Info.
Im Blatt "Mitglieder" habe ich 3 Hilfsspalten angelegt, die darfst Du nicht löschen, da sonst nix mehr funktioniert.

Das in den Spalten G bis J kannst Du löschen, dort sind die Formeln drin die auch in den anderen Blättern drin sind, genauer gesagt die aus dem Blatt Herren. Damit Du beim studieren nicht andauernd zwischen den Blättern hin und her wechseln musst, sind sie dort.

In den Spalten I und J sind Matrixformeln drin, die den Namen UND den Vornamen suchen, und auf Grund des Ergebnis dann einerseits den Ort und anderseits das Alter ausgeben.

Ich hoffe Du kommst zu recht.



Gruß
balu


[edit]
Sorry, hatte falsche Datei angehängt. Ist nun korrigiert.

[boevent]

WOW

Vielen vielen Dank.

Das sieht super aus! Und ist bis auf Kleinigkeiten genau was ich brauche

Mir ist nur aufgefallen das ich mich unklar ausgedrückt habe. Denn in der ersten Sparte kann auch Mixies stehen oder in der zweiten Herren. Die Reihenfolge soll beliebig sein. Und es gibt auch noch mehr Sparten. Ich habe es nur stark verkürzt, damit es übersichtlciher scheint.

Ich habe mich da selbst mal rangemacht und die Formel in dem Hilfsteil angepasst, so dass alle "Herren" berücksichtigt werden, egal ob in Spalte "C" oder "D":

Code: Alles auswählen

=WENN(ODER((C5<>"")*(C5=$L$1);(D5<>"")*(D5=$L$1));ZÄHLENWENN(C$4:C5;$L$1)+ZÄHLENWENN(D$4:D5;$L$1);"")

Doch diese Formel kann ich dann nicht mehr runterziehen. Deine Formeln kann man einwandfrei herunterziehen (vervielfältigen) jedoch mit meiner abgeänderten geht das irgendwie nicht?

Vielen Dank!!!
LG
Thomas

[boevent]

Noch mal konkreter:

Warum kann ich die erste Formel herunterziehen und die zweite nicht?

1)

Code: Alles auswählen

=WENN((D4<>"")*(D4=$N$1);ZÄHLENWENN(D$4:D4;$N$1);"")

2)

Code: Alles auswählen

=WENN(ODER(($C4<>"")*($C4=$L$1);(($D4<>"")*($D4=$L$1)));ZÄHLENWENN($C$4:$C4;$L$1)+ZÄHLENWENN($D$4:$D4;$L$1);"")

Kopieren der Formel funktioniert aber, von daher ist das genau die Lösung die ich gesucht habe! Also nochmals vielen Dank ins Besondere an balu!!!

[balu]

Hallo Thomas,

Warum kann ich die erste Formel herunterziehen und die zweite nicht?

Ich weiß nicht wo bei deiner letzt genannten Formel dein Problem mit dem runterziehen ist. Bei mir funktioniert sie. Du hast wohl ein Klammer-Paar überflüssigerweise drin, das aber zumindest bei mir hier keine Rolle spielt.

Versuchs jetzt mal hiermit.

Code: Alles auswählen

=WENN(ODER((C4<>"")*(C4=$L$1);(D4<>"")*(D4=$L$1));ZÄHLENWENN(C$4:C4;$L$1)+ZÄHLENWENN(D$4:D4;$L$1);"")

Was für ein Betriebssystem hast Du, und welche OOo Version?

Also nochmals vielen Dank ins Besondere an balu!

Gern geschehn .


Gruß
balu

[boevent]

Hallo,

ich habe Windows 7 mit der neusten Version von Oo. Heute erst aktualisiert (Version 3.3.0).

Aber auch mit der neuen Formel geht es nicht! Sehr merkwürdig! Aber ich kopiere sie einfach mit Strg+c und füge sie dann ein. Wenn ich 10 kopiert habe kopiere ich 10 auf einmal... Also arbeit überschaubar

Aber eine letzte Frage wäre da noch gezielt an dich balu:

Warum hast du für Namen und Vornamen diese Formel:

Code: Alles auswählen

=WENN(ISTFEHLER(KKLEINSTE($Mitglieder.$L$4:$L$17;ZEILE(A1)));"";INDEX($Mitglieder.$A$4:$A$17;VERGLEICH(KKLEINSTE($Mitglieder.$L$4:$L$17;ZEILE(A1));$Mitglieder.$L$4:$L$17;0)))

Und für die anderen Daten wie Ort und Alter dann diese:

Code: Alles auswählen

=INDEX($E$4:$E$17;VERGLEICH($G4&$H4;$Mitglieder.$A$4:$A$17&$Mitglieder.$B$4:$B$17;0))

Macht sich das so in der Ladezeit bemerkbar, wenn ich die erste Formel für alle Felder nehme? Denn ich könnte sie ja für alle kopieren und dann funktioniert es auch korrekt! Habe die erste Formel so angepasst mit den $, dass ich sie auch nach rechts ziehen kann. Ist das nicht empfehlenswert?

Vielen Dank!

P.S.: Ich werde die fertige Tabelle hier später noch mal anhängen (wenn sie fertig ist ). Vlt. kann es ja wer gebrauchen!

[balu]

Hallo Thomas,

Warum hast du für Namen und Vornamen diese Formel:

Also doch viele Worte meinerseits. Mir bleibt aber auch nix erspart .

Kurzversion.
1.
Zur Unterdrückung von Fehlermeldungen!

2.
Irgendwie muss ich ja an die Namen rankommen, da ja alles weitere sich auf die Namen bezieht.

Etwas ausführlicher.
Schaun wir uns mal den ersten Teil der Formel an.

Code: Alles auswählen

WENN(ISTFEHLER(KKLEINSTE($Mitglieder.$L$4:$L$17;ZEILE(A1)));"";

Wenn keine Zahl mehr in der Spalte L gefunden wird, dann würde KKLEINSTE eine Fehlermeldung ausgeben. Das kannst Du dadurch feststellen wenn Du irgendwo, z.B. in B20 einfach folgendes eingibst.

Code: Alles auswählen

=KKLEINSTE($Mitglieder.$L$4:$L$17;8)

Das Eergebnis ist die Fehlermeldung #WERT!
Warum?
Weil die letzte Zahl in der Spalte L gleich die 5 ist (in meiner Beispieldatei). Da gibt es keine Zahl die größer als die 5 ist. Ergo kommts zur Fehlermeldung.
In dieser Beispielformel ist der zweite Parameter (Rang_K) fest auf den Wertt 8 gesetzt. In der Originalformel steht dort aber ZEILE(A1). Und durch das runterziehen ändert sich ZEILE. Da wird dann aus A gleich B, C, D ...
ZEILE gibt die Zeilennummer zurück, so das dann A=1, B=2 etc. wird.

Und irgendwann gibt ZEILE eine Zeilennummer zurück, die nicht mehr identisch mit den vorhanden Zahlen in der Spalte L ist. Also Fehlermeldung. Diese Fehlermeldung wird aber durch ISTFEHLER abgefangen, und eine Leere Zelle ausgegeben.

Wenn aber KKLEINSTE einen Wert zurückgibt, der mit der Spalte L identisch ist, dann kommt der nächste Teil dran.

Code: Alles auswählen

INDEX($Mitglieder.A$4:A$17;VERGLEICH(KKLEINSTE($Mitglieder.$L$4:$L$17;ZEILE(A1));$Mitglieder.$L$4:$L$17;0)))

Hier hab ich jetzt mal für INDEX den 1. Parameter geändert $Mitglieder.A$4:A$17, so das sie nach rechts rezogen werden kann. Aber auch nur einmal!

Und für die anderen Daten wie Ort und Alter dann diese:

Das muss ja auch so sein!
Denn erst wenn Name UND Vorname rausgefiltert sind, hat man eine gescheite Suchbasis. Nur nach Name oder Vorname suchen bringt ja nichts, da ja das eine oder andere Doppelt vorhanden sein kann. Und die zweite Formel ist eine Matrixformel, da man nur so bei VERGLEICH 2 Suchkriterien miteinander verketten kann.

Macht sich das so in der Ladezeit bemerkbar, wenn ich die erste Formel für alle Felder nehme?

Welch seltene Frage, wegen der Ladezeit. Ich würd aber sagen, nö.
Du kannst NICHT die erste Formel für alle nehmen! Das geht nicht! Es geht um zwei verschiedene Paar Schuhe, die man nicht gleichzeitig anziehen kann.

Denn ich könnte sie ja für alle kopieren und dann funktioniert es auch korrekt!

Das bezweifle ich doch sehr stark!
Beweise mir das Gegenteil durch deine modifizierte Datei!

Habe die erste Formel so angepasst mit den $, dass ich sie auch nach rechts ziehen kann.

Ja aber nur um Name und Vorname rauszubekommen. Mehr nicht!

Ist das nicht empfehlenswert?

So ist es!


Zu deiner Frage

Warum kann ich die erste Formel herunterziehen und die zweite nicht?

ist mir jetzt noch was eingefallen.
Wenn Du die Formel runterziehst, bekommst Du dann die Fehlermeldung #NAME? ?
Bin mir wohl nicht sicher, aber ich möchte dafür Wetten das Du die besagte Formel als Matrixformel abgeschlossen hast, also mit "Strg" + "Shift" + "Enter". Und das ist in diesem Falle ein fataler Fehler! Weil es sich in diesem Falle nämlich um KEINE Mtrixformel handelt, sondern um eine ganz normale.

Nur die für Ort (Spalte I) und für Alter (Spalte J) sind Matrixformeln!

Ein extrem wichtiger Tipp bezüglich Matrixformeln!
Wenn Du solche runterziehen willst, MUSST Du die Strg-Taste dabei festhalten!
Und noch einer.
Nur wenn Du dir absolut sicher bist das es sich um eine Matrixformel handelt, dann musst Du dementsprechend handeln. Mach aber blos nicht aus jeder x-beliebiegen Formel eine Matrixformel, denn das kann sonst böse enden!

Denk bitte an deine modifizierte Beispieldatei.

Ich arbeite übrigens unter WinXP, mit OOo 3.2.1, 3.3.0, LO 3.4.0 etc. .... Aber kein Excel.


Gruß
balu

[boevent]

Puhhh Danke!
Was ich heute alles lerne! Und ich dachte mit Calc das klappt schon ganz gut

Also voll ins schwarze getroffen. Hab tatsächlich jede Formel schön mit STRG + Shift + Enter abgeschlossen

Aber es scheint dennoch iwie zu funktionieren (siehe Anhang)!

[balu]

Hallo Thomas,

Was ich heute alles lerne! Und ich dachte mit Calc das klappt schon ganz gut

Man wird alt wie ein Haus, und lernt nicht aus.
Das trifft auch auf mich zu

Also voll ins schwarze getroffen. Hab tatsächlich jede Formel schön mit STRG + Shift + Enter abgeschlossen

Wäre auch verwunderlich wenn dem nicht so gewesen wäre. Zumal ich das ja selber hier getestet hatte.

Aber es scheint dennoch iwie zu funktionieren (siehe Anhang)!

Jaaaa die Datei ist ja jetzt auch nicht mehr mit meiner Beispieldatei zu vergleichen. Und von daher passt dass dann auch schon fast.
Aber warum machst Du denn jetzt wieder aus normalen Formeln, Matrixformeln? Das ist überflüssig, und ich versteh es nicht!

Code: Alles auswählen

=WENN(ISTFEHLER(KKLEINSTE($Mitglieder.$P$3:$P$150;ZEILE(A1)));"";INDEX($Mitglieder.A$3:A$150;VERGLEICH(KKLEINSTE($Mitglieder.$P$3:$P$150;ZEILE(A1));$Mitglieder.$P$3:$P$150;0)))

Code: Alles auswählen

=WENN(ODER(($L3<>"")*($L3=P$1);(($M3<>"")*($M3=P$1));(($N3<>"")*($N3=P$1)));ZÄHLENWENN($L$3:$L3;P$1)+ZÄHLENWENN($M$3:$M3;P$1)+ZÄHLENWENN($N$3:$N3;P$1);"")

Das sind KEINE Matrixformeln!
Um merkwürdige Verhalten der Datei zu vermeiden, lass das mit den Matrixformeln sein! Denn ich hatte nämlich vorhin das komische Phänomen, dass die Daten nicht richtig verwertet wurden. Obwohl so weit alles in Ordnung war, wurde in einem Blatt das Geburtsdatum NICHT angezeigt. Es lag eigentlich kein Fehler vor. Erst als ich die Formeln wieder normal gemacht hatte, funktionierte es einwandfrei.

Glaub mir, wenn es keine Matrixformeln sind, dann mach auch keine daraus. Du handelst dir nur Ärger ein. Also gib die Formeln ganz normal mit Enter ein, mehr nicht.


Gruß
balu

[boevent]

Ja das mit den Matrixformeln wusste ich ja nicht! Hatte ich ja schon weiter oben geschrieben. Seit dem hatte ich nichts mehr an der Datei geändert!
Ich werde jetzt alle Formeln die Formeln sind wieder Formeln sein lassen Das Matrix werde ich eleminieren!

Ich war so beeindruckt durch das Phänomen Matrixformeln, dass ich eben nicht mehr richtig mitgedacht habe

Abschließend noch mal vielen Dank für deine kurzen und deine langen Erläuterungen So viel Hilfe ist sehr lobenswert. Und das man ganze Lösungen zur Verfügung stellt ist auch nicht selbstverständlich!!!

Bis zum nächsten mal

[balu]

Hallo Thomas,

Ich war so beeindruckt durch das Phänomen Matrixformeln, dass ich eben nicht mehr richtig mitgedacht habe

Kommt schon mal vor. Is ja nun auch halb so wild.

Und das man ganze Lösungen zur Verfügung stellt ist auch nicht selbstverständlich!!!

Na ja, ganze Lösung wars ja nun halt doch nicht. Denn Du hast meine Vorschläge genommen, angepasst und ausgebaut. Und so etwas sehen alle Helfer gern, wenn der Hilfesuchende aktiv mit macht.

Danke für die Blumen .


Gruß
balu