Office: Gültigkeits Liste aus einer Übersichtsmatrix

Da einem hier sooo schnell geholfen wird, habe ich gleich noch eine Frage...

Ich habe eine Knowledge Matrix:
In Spalte A stehen die Namen der Mitarbeiter, in Spalte B die Mailadressen, in Spalte C die Telefonnummern und ab Spalte D die Themen...

In der Spalte des Themas und der Zeile des Mitarbeiters ist ein "x", wenn dieser das Thema beherrscht.

Jetzt gibt es für jedes Thema ein Tabellenblatt mit identischen Namen.

In dem Jeweiligen Tabellenblatt möchte ich eine Dropdown Auswahl aber nur von den Mitarbeitern die dieses Thema beherrschen, also in der Matrix ein "x" haben.

Siehe Beispiel Tabelle...[/b]

Schon mal DANKE :!:

 

Hallo!

Schreibe in A1 (oder sonstwo) diese Formel und schließe die Eingabe mit Strg+Shift+Enter ab:

=WENN(ZEILE(A1)>ZÄHLENWENN(Mitarbeiter!D:D;"x");"";INDEX(Mitarbeiter!A$2:A$100;KKLEINSTE(WENN(Mitarbeiter!D$2:D$100="x";ZEILE($1:$99));ZEILE(A1))))

Diese Formel füllst du nun nach unten aus und du hast die Quelle für deine Gültigkeitsliste.

 

Das werde ich mal Versuchen, Danke!

Aber so brauche ich immer diesen Zwischenschritt mit dem Extra Bereich für die Gültigkeitsliste, diesen Schritt wollte ich mir gerne ersparen...

Gibt es vielleicht noch eine andere Möglichkeit?

Evtl. wenn man statt des "x" den Namen der Mitarbeiter einfügt, vielleicht durch ein Macro, welches das "x" durch den Eintrag in der Aktuellen Zeile und Spalte A ersetzt oder so ähnlich?

 

Hallo!

Für eine Gültigkeitsliste brauchst du einen zusammen hängenden Bereich als Quelle.
Wenn du diese Spalte dann ausblendest, dann düfte sie doch eigentlich nicht mehr stören, oder?

 

Ok, da hast du Recht.

und wie sieht es mit der Listenlänge aus, wenn Mitarbeiter hinzukommen verändert sich ja die Länge der Liste. Da gibt es doch bestimmt die Möglichkeit diese Dynamisch anzupassen?

 

Hallo!

Die Formel kannst du großzügig nach unten kopieren.
Wenn dich die Leerzeilen im Gültigkeits-DropDown stören, dann kannst du dem Quellbereich einen dynamischen Bereichsnamen zuweisen:

Menü Einfügen > Namen > Definieren
Name: Liste1
Bezieht sich auf: =BEREICH.VERSCHIEBEN($A$1;;;SUMME(N($A$1:$A$1000<>"")))

(wenn deine Quellliste in A1 beginnt)

Diesen Namen gibst du dann in der Gültigkeit als Quelle an:

Quelle: =Liste1

 

Hallo!

Ich habe mal ein wenig mit den Formeln probiert, und es Funktioniert auch alles. Dafür noch mal ein riesiges Danke! :!:

Jetzt kommt das "aber"...

Seit dem einfügen der Array Formel ist die Tabelle sehr langsam geworden, also bei jeder Eingabe in eine beliebige Zelle in einem beliebigen Blatt werden alle Zellen neu berechnet, das ist der Grund warum ich nach Möglichkeit auf Array Formeln verzichte.

In der Tabelle befinden sich z.Zt. 25 Blätter, auf jedem ist die Array Formel 100x runterkopiert...

Gibt es vielleicht doch eine Alternative ohne Array, gerne auch per Macro!

Das ausschalten der Automatischen Berechnung kann ich nicht zur Beschleunigung einsetzen, da die user die am Ende damit arbeiten sollen das nicht verstehen würden... :cry:

 

Hallo!

Ja, bei dieser Größe bremst das natürlich aus...
Klemm doch mal diesen Code in das Modul von "Thema-1":

Code:

Private Sub Worksheet_Activate()
Dim i As Long, Ende As Long
Dim Liste As String
Liste = ""
With Sheets("Mitarbeiter")
Ende = .Range("A65536").End(xlUp).Row
For i = 2 To Ende
If .Cells(i, 4) = "x" Then Liste = Liste & .Cells(i, 1).Text & ","
Next i
End With
Liste = Left(Liste, Len(Liste) - 1)
With Range("A1").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlEqual, Formula1:=Liste
End With
End Sub

Der Code für deine weiteren Blätter benötigt dann nur diese Anpassungen:
If .Cells(i, 5) = "x", wenn Spalte E durchsucht werden soll, usw.