Wir haben uns ja schon im Dropdown like its hot Post eindringlich mit Excel Dropdownlisten / Auswahllisten beschäftigt. In diesem Artikel befassen wir uns mit der Königsdisziplin des Dropdowns:
- Die Dropdownliste soll jedes Element nur einmal zulassen bzw. nachdem ein Element ausgewählt wurde, soll es nicht mehr in der Liste aufgeführt werden
- Die Dropdownliste beinhaltet keine Leerzeichen sondern nur die verbleibenden Auswahlmöglichkeiten
Dies hat zur Konsequenz, dass die Auswahlmöglichkeiten innerhalb des Dropdowns mit jeder Auswahl in den vorangegangen Zellen kleiner wird.
Ist ja ne tolle Sache, aber wofür sollte ich das denn gebrauchen können?
Nun ja, mir fallen spontan hier unterschiedlichste Excel Vorlagen ein, z.B. Schichtpläne/Dienstpläne.
Sobald es um eine Planung geht, bei der bestimmte Elemente nur einmal vergeben werden können (z.B. Arbeitskraft in einem bestimmten Zeitfenster), ist es sinnvoll die Auswahlmöglichkeit im Dropdown zu beschränken. Ansonsten läufst du Gefahr ein Element evtl. mehrfach einzuplanen.
Keine Lust zu lesen? Schau dir das Tutorial an
Excel Vorlage herunterladen und einfach mitmachen
Erfolgreiche Manager in Unternehmen setzen auf automatisierte Dashboards
Das Geheimnis von erfolgreichen Managern liegt in der Vereinfachung von Prozessen. Höre auf mit manuellen Excel Berichten und Vorlagen und steige ein in die Welt der automatisierten Excel Dashboards!
Schritt für Schritt zum fertigen Dropdown der Königsklasse
Wir starten dieses Tutorial mit einem noch zu befüllenden Schichtplan. Ziel ist es jeder Position einen Mitarbeiter zuzuweisen (in den Zellen B6:B16)
Schritt 1: Erstelle eine Liste für dein Dropdownfeld
Im ersten Schritt erstellen wir 3 Hilfsspalten, die wir für die Befüllung des späteres Dropdown Menüs benötigen.
In die erste Spalte schreiben wir alle Elemente hinein, die später als Auswahlmöglichkeit im Dropdown zu finden sein sollen.
In unserem Fall sind es 10 Namen, die wir in die erste Spalte hineinschreiben.
Schritt 2: Lass Excel herausfinden, ob der Name schon verwendet wurde
Wir steigen nun in die zweite Hilfsspalte ein. Unter "belegt = leer" soll nun die Zeile ausgewiesen werden, in der die Namen aufgeführt sind. Jedoch nur wenn der Name nicht schon im Schichtplan eingetragen wurde.
Die Formel hierzu sieht folgendermaßen aus:
=WENN(ZÄHLENWENN($B$6:$B$16;P6)>=1;"";ZEILE())
Beschreibung der Formel:
WENN der Bereich, in dem später die Namen stehen sollen, den Namen in unserer Hilfsspalte enthält, DANN lasse die Zelle leer; ANSONSTEN gebe mir den Zeilenwert des Namens.
Sobald ihr die Formel herunterzieht, solltet ihr folgendes Bild erhalten (unter der Voraussetzung, das noch keine Namen im Schichtplan stehen):
Die Zahlen in der Spalte "belegt = leer" stellen die Zeilennummern dar. Wenn z.B. Bernd in dem Schichtplan schon eingesetzt wurde, dann würde hier eine leere Zelle erscheinen.
Schritt 3: Sortiere die noch nicht verwendeten Namen und entferne Leerzeichen zwischen den Namen
Nun befüllen wir die dritte Hilfsspalte "noch frei".
Hierzu verwenden wir eine relative lange Formel, die nicht nur die schon verwendeten Namen aussortiert, sondern auch Leerzellen zwischen den Namen entfernt.
=WENN(ZEILE(P6)-ZEILE(P$6)+1>ANZAHL($Q$6:$Q$15);"";INDEX(P:P;KKLEINSTE($Q$6:$Q$15;1+ZEILE(P6)-ZEILE($P$6))))
Beschreibung der Formel:
WENN die jeweilige Zeile abzüglich der Zeile des ersten Namen +1 größer ist als die Gesamtanzahl der noch verfügbaren Zeilennummern; DANN lasse die Zelle leer; ANSONSTEN weise den Namen mit dem Kleinsten Zeilenwert aus.
Ich weiß... Ich weiß das ist auf keinen Fall beim ersten Mal durchlesen verständlich. Man muss ja nicht alles verstehen, was man so in eine Excel Zelle reinkopiert ;)
In Folgendem Szenario habe ich nun schon Bernd und Robert im Schichtplan eingetragen und die Formel heruntergezogen, damit du sehen kannst, wie sich die Formel auswirkt.
Schritt 4: Vergebe einen Namen für deinen Dropdown Input um ein Dropdownfeld ohne Leerzeichen zu erhalten
Wir vergeben nun einen Namen für die zu verknüpfenden Daten. Hierbei verwenden wir einen die Bereich.verschieben Formel (für mehr Infos hierzu, geht auf meinen Top 5 Formel Beitrag), Diese gewährleistet uns, das wir keine Leerzeichen im Excel Dropdownfeld sehen werden.
Hierzu gehst du in den Namensmanager, vergibst einen Namen und fügst folgende Formel ein:
=BEREICH.VERSCHIEBEN('KW1'!$R$6;0;0;ANZAHL('KW1'!$Q$6:$Q$15);1)
Natürlich musst du die Formel deinem Tabellennamen entsprechend anpassen.
Schritt 5: Dropdown erstellen
Wir schließen unser Vorhaben mit der eigentlich Erstellung des Dropdowns ab.
Hierzu gehst du über Daten --- Datenüberprüfung und wählst dort bei den Gültigkeitskriterien die Liste aus.
Als Quelle gibst du nun einfach den vergebenen Namen ein aus Schritt 4.
Du hast es geschafft!
Zusammengefasst
Mit einigen Tricks konnten wir unser perfektes Dropdown in Excel erstellen. Ich gebe zu es bedarf schon einiger Schritte dies umzusetzen und bei großen Excel Templates könntest du dich schnell in den Hilfskalkulationen verlieren. Hast du jedoch ein ziemlich einfach Template, werden sich deine Kollegen über dieses kleine Feature freuen.
Wenn du mehr zu Dropdowns erfahren willst, checke doch diesen Artikel aus.
Kommentar schreiben
Günther (Dienstag, 24 März 2020 17:06)
Hallo Excel König!
Dieses DropDown Video ist toll und würde für meine Anwendung perfekt passen!
Ich habe nur das Problem, wenn ich den Schritt 5 eingebe, dann kommt eine Fehlermeldung:
"Die Quelle untersucht gerade einen möglichen Fehler. Möchten Sie den Vorgang fortsetzen?"
Gibts dafür eine Lösung?
Danke im Voraus
Michael Siebert (Freitag, 28 August 2020 11:19)
Es liegt wohl daran, dass dein Tabellenblatt nicht KW1 heißt. =BEREICH.VERSCHIEBEN('KW1'!$R$6;0;0;ANZAHL('KW1'!$Q$6:$Q$15);1)
Ich habe es in einer nicht gespeicherten Tabelle nachgebaut.
Die Formel bei mir lautet:
=BEREICH.VERSCHIEBEN(Tabelle1!$R$6;0;0;ANZAHL(Tabelle1!$Q$6:$Q$15);1)
Dann funktioniert es.
Marcel Geist (Dienstag, 08 September 2020 01:32)
Guten Tag,
ich habe bedauerlicherweise bei Punkt ein Problem.
Habe diese Formel eingegben:
=WENN(ZEILE(P6)-ZEILE(P$6)+1>ANZAHL($Q$6:$Q$15);"";INDEX(P:P;KKLEINSTE($Q$6:$Q$15;1+ZEILE(P6)-ZEILE($P$6))))
Habe ein neues Datenblatt angelet, mit exakt den selben Koordinaten um jeglichen Fehler auszuschließen.
In Zelle R6 kommt folgen Meldung : #NAME?
Haben Sie einen Tipp für mich?
Besten Dank im Vorraus
Marcel G.
Christian Schulz (Sonntag, 21 März 2021 09:21)
Moin, in der Beschreibung fehlt der Hinweis, dass R6-15 die Liste für den Dropdown ist. Die Formeln fuktionieren auch ohne Schritt 4 sehr gut.
Ralf (Donnerstag, 01 April 2021 08:41)
Hallo,
das ist ja nun nur für einen Tag.
Wie erweitere ich es auf die ganze Woche, muß ich da tatsächlich für jeden Tag eine eigene Liste in Spalte P-R anlegen?
Patrick (Freitag, 28 Mai 2021 20:57)
Hallo,
ich finde die Frage von Ralf sehr interessant, gibt es eine Möglichkeit das ganze auf eine 7 Tage Woche auszurollen?
Natürlich ohne 7 mal die Spalte P-R anzulegen.
Lori (Mittwoch, 01 Dezember 2021 09:22)
Hallo,
mich stört in der Formel "=WENN(ZEILE(P6)-ZEILE(P$6)+1>ANZAHL($Q$6:$Q$15);"";INDEX(P:P;KKLEINSTE($Q$6:$Q$15;1+ZEILE(P6)-ZEILE($P$6))))"
das "P:P", könnte man das nicht ersetzen durch "$P$6:$P$15" ? Vielleicht stehen in der Spalte ja noch andere Daten. Bei mir kommt jedoch dann ein anderes Ergebnis?
M (Freitag, 14 Januar 2022 19:05)
Hallo, Prima Internetseite sensationelle Videos... DANKE!
PS. als absoluter Anfänger kann ich die Liste "=Schicht1" nicht finden ..?! oder besser gesagt wie kann ich die Anzahl der Dropdownliste auf z.B. 20 erhöhen?
Liebe Grüße
M (Montag, 17 Januar 2022 06:20)
Liste Schick 1 unter Formel... Die Mitarbeiter in 2 verschiedenen Spalten funktioniert allerdings leider nicht.. �
Uwe (Freitag, 18 März 2022 19:52)
Schön, dass hier auch ein paar Videos eingebettet sind. Habe meine Tabelle so aufgebaut, dass ich die Formeln nicht mal ändern muss. Aber trotzdem steht bei mir in den Hilfsspalten der größte Müll, aber nicht das, was nach den Beispielen hier stehen sollte, bis hin - wie bereits erwähnt - die besagte Meldung "Die Quelle untersucht gerade einen möglichen Fehler. Möchten Sie den Vorgang fortsetzen?" Und das Dropdownfeld funktioniert auch nicht mehr. Der Dropdownpfeil ist da, aber das "Dropdown" funktioniert nicht mehr.
Sebsatian (Donnerstag, 09 Juni 2022 16:46)
Hallo,
bei mir hat es sehr gut funktioniert. Ich habe es für eine Einnahmen-Überschuss-Tabelle verwendet, um die Nummern für Ein- sowie Ausgangsrechnungen nicht doppelt zu vergeben.
Danke für den Tipp.
Ralf (Freitag, 05 August 2022 12:54)
Werte über Dropdown auswählen -> läuft
Werte über Eingabe eingeben, da kommt dann immer Fehlermeldung "Der eingegebene Wert ist ungültig. Ein anderer Benutzer hat die Werte begrenzt, die in diese Zeile eingegeben werden können."
Was mach ich verkehrt?
Onkel Dolf (Donnerstag, 16 März 2023 15:38)
Germani
Für Fortnite AMK
Lutz (Samstag, 18 März 2023 17:03)
Prima Anleitung! Funktioniert auch. Ich möcht aber auch in die Eingabefelder Direkteingaben machen können. Das sind nur einzelne Buchstaben. Die direkte Eingabe geht schneller als das Dropdown. Jemand eine Lösung dafür?
Simon (Sonntag, 02 April 2023 22:24)
Super Anleitung. Funktioniert sehr gut, jedoch habe ich folgendes Problem: Ich möchte aus der Dropdownliste einen beliebigen Namen auswählen. Dieser verschwindet dann nicht aus der Auswahlliste im Dropdown. Dies erfolgt imme rnur, wenn ich von oben nach unten in der Liste die Namen eintrage. Mache ich irgendetwas falsch?
Dominika (Donnerstag, 02 November 2023 20:53)
Wie soll ich die Formel editieren wenn ich mehrere gleiche Namen habe die ich eintragen muss, in die gleiche Spalte? Dropdown zeigt es mir mehrfach an, wenn ich es aber einmal auswähle ist der von die Liste weg.
Danke für die Hilfe!
Liebe Grüße
Dominika
Philipp (Freitag, 19 Januar 2024 12:08)
Kann man die Formel aus Schritt 2 =WENN(ZÄHLENWENN($B$6:$B$16;P6)>=1;"";ZEILE()) auch auf zwei Suchbereiche ausweiten?
Bei mir sieht der Spaß so aus:
=WENN(ZÄHLENWENN(Tabelle1[Spalte1];A6)>=1;"";ZEILE())
Ich müsste jedoch zugleich einen weiteren Bereich prüfen.
=WENN(ZÄHLENWENN(Tabelle2[Spalte1];A6)>=1;"";ZEILE())
Nur eben beides gleichzeitig.
Ich habe probiert =WENN(ZÄHLENWENN(Tabelle1[Spalte1](Tabelle2[Spalte1];A6)>=1;"";ZEILE())) daraus zu machen, oder die beiden Bereiche mit einem "+" "," oder "." zu verbinden. Bsp. (Tabelle1[Spalte1]+(Tabelle2[Spalte1];A6)
Das Funktioniert so nur alles leider nicht.
Kann das was ich vorhabe funktionieren, und wenn ja wie?