Das perfekte Excel Dropdown ohne bereits verwendete Elemente und Leerzeichen

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

Excel Vorlage: Dropdowns ohne bereits verwendete Elemente

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)

Excel Schichtplan Vorlage mit Dropdownfelder

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.

Excel Drop Down Namensliste

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())

Excel Auswahlliste Element darf nur einmal verwendet werden

 

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):

Excel Dropdown Liste ohne Leerzeichen

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))))

Excel Dropdown ohne Leerzeichen und schon benutzten Werten

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.

Excel Dropdown ohne bereits benutze Werte

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.

Excel Dropdown ohne Leerzeichen

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. 

Excel Datenüberprüfung

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.

Weitere Tutorials

Kommentar schreiben

Kommentare: 0