Wie blendet man zuvor verwendete Elemente in einer Dropdown-Liste aus?
In Excel ist das Erstellen einer standardmäßigen Dropdown-Liste eine gängige Methode zur effizienten Dateneingabe. Doch was, wenn die Liste nach jeder Auswahl dynamisch kürzer werden soll – also bereits ausgewählte Elemente automatisch entfernt werden, sodass jede Option nur einmal wählbar ist? Stellen Sie sich beispielsweise eine Dropdown-Liste mit 100 eindeutigen Namen vor: Sobald Sie einen Namen auswählen, verschwindet er aus der Liste, und es bleiben nur noch 99 Optionen. Mit jeder weiteren Auswahl schrumpft die Liste automatisch weiter, bis schließlich keine Auswahl mehr möglich ist. Diese Art interaktiver Steuerung ist besonders wertvoll in Szenarien wie der dopplungsfreien Aufgabenverteilung, der Sitzplatzplanung oder Lotterieziehungen, bei denen jede Auswahl einzigartig sein muss. Excel bietet diese Funktion jedoch nicht von Haus aus – spezielle Workarounds sind daher erforderlich. In den folgenden Abschnitten finden Sie detaillierte Schritt-für-Schritt-Anleitungen, wie Sie dieses Verhalten erfolgreich umsetzen können.
Zuvor verwendete Elemente in Dropdown-Liste mithilfe von Hilfsspalten ausblenden
Zuvor verwendete Elemente in Dropdown-Liste mithilfe von Hilfsspalten ausblenden
Angenommen, Sie haben eine Namensliste in Spalte A, wie im folgenden Screenshot gezeigt. Um eine Dropdown-Liste einzurichten, die bereits verwendete Einträge automatisch ausblendet, folgen Sie einfach den nachstehenden Schritten. Dabei kommen zusätzliche Hilfsspalten zum Einsatz, um zu erfassen, welche Elemente bereits ausgewählt wurden, und um daraus dynamisch die Quellliste für die Dropdown-Liste zu generieren. Auch wenn dieser Ansatz auf den ersten Blick etwas aufwendig wirken mag, lässt er sich problemlos umsetzen – und das ganz ohne Programmierkenntnisse.
Anwendbare Szenarien umfassen Terminplanung, Ressourcenzuweisung oder jeden Fall, in dem ein Element nur einmal ausgewählt werden darf, bis die Liste erschöpft ist. Der Vorteil liegt in der klaren, nachvollziehbaren Logik und den sichtbaren Formeln – allerdings erfordert dies die Pflege zusätzlicher Spalten in Ihrem Arbeitsblatt.
1. Geben Sie neben Ihrer Namensliste in Zelle B1 die folgende Formel ein, um zu prüfen, ob ein Name bereits im Zielbereich der Dropdown-Liste ausgewählt wurde:
=IF(COUNTIF($F$1:$F$11,A1)>=1,"",ROW())Diese Formel vergleicht jeden Namen mit der in der Dropdown-Liste getroffenen Auswahl (Bereich F1:F11). Wurde der Name bereits ausgewählt, gibt sie eine leere Zelle zurück; andernfalls liefert sie die Zeilennummer als Hilfswert. Passen Sie unbedingt den Bereich F1:F11 an den Ort an, an dem Ihre Dropdown-Liste platziert werden soll, und die Referenz A1 an den Standort Ihrer Namensliste.
Hinweis: Stellen Sie sicher, dass der Bereich „F1:F11“ alle Dropdown-Zellen enthält. Die Referenz „A1“ sollte auf die aktuelle Zeile Ihrer Namensliste verweisen.
2. Ziehen Sie das Ausfüllkästchen nach unten, um diese Formel auf alle Zeilen Ihrer Namensliste anzuwenden. So erhalten Sie eine Reihe von Hilfsergebnissen, die nicht verwendete Namen identifizieren.
3. Richten Sie in Zelle C1 (Spalte C) eine weitere Hilfsformel ein, um dynamisch eine bereinigte Liste aller nicht verwendeten Namen zu erstellen:
=IF(ROW(A1)-ROW(A$1)+1>COUNT(B$1:B$11),"",INDEX(A:A,SMALL(B$1:B$11,1+ROW(A1)-ROW(A$1))))Diese Formel erfasst alle ungenutzten Namen, indem sie die entsprechenden Einträge aus Spalte A anhand der Hilfswerte in Spalte B herausfiltert. Sobald Sie Namen auswählen und aus Spalte B entfernen, aktualisiert sich die Liste in Spalte C automatisch. Sollte Ihre Liste mehr als 11 Namen enthalten, passen Sie bitte alle Bezugsbereiche entsprechend an.
4. Kopieren Sie diese Formel nach unten, bis sie genau so viele Zeilen umfasst wie Ihre ursprüngliche Namensliste. Der ausgefüllte Bereich sollte genauso lang sein wie Ihre Liste in Spalte A.
5. Um diese dynamisch aktualisierte Liste für Ihre Dropdown-Liste nutzbar zu machen, definieren Sie einen benannten Bereich. Wählen Sie die neu erstellte Liste in Spalte C (z. B. C1:C11) aus und klicken Sie anschließend auf Formeln > Namen definieren.
6. Geben Sie im Dialogfeld Neuer Nameeinen Namen ein (z. B.)namecheck) und verwenden Sie die folgende dynamische Bezugsformel, um sicherzustellen, dass der benannte Bereich stets korrekt dimensioniert bleibt, sobald Namen ausgewählt werden:
=OFFSET(Sheet2!$C$1,0,0,COUNTA(Sheet2!$C$1:$C$11)-COUNTBLANK(Sheet2!$C$1:$C$11),1)Dadurch wird sichergestellt, dass ausschließlich nicht leere Werte aus Spalte C für die Dropdown-Optionen verwendet werden. Prüfen Sie sorgfältig Ihren Arbeitsblattnamen und Ihre Zellbezüge – achten Sie auf exakte Adressformate –, damit diese Ihrem eigenen Arbeitsblatt entsprechen.
Hinweis: Wenn Sie die Namensliste ändern, Zeilen hinzufügen oder entfernen oder ein anderes Arbeitsblatt verwenden, passen Sie die Formel entsprechend an, um Fehler zu vermeiden.
7. Um nun die eigentliche Dropdown-Liste zu erstellen, markieren Sie die Zellen, in denen Benutzer ihre Auswahl treffen sollen (z. B. F1:F11). Gehen Sie zu Daten > Datenüberprüfung > Datenüberprüfung.
8. Wählen Sie im Dialogfeld Datenüberprüfung unter der Registerkarte Einstellungen die Option Liste aus und geben Sie =namecheck in das Feld „Quelle“ ein, um auf den dynamischen benannten Bereich zu verweisen, den Sie definiert haben.
Klicken Sie auf OK, um den Vorgang abzuschließen. Sobald ein Name in der Dropdown-Liste ausgewählt wurde, wird er in den anderen Dropdowns ausgeblendet – so bleiben alle Auswahlmöglichkeiten eindeutig. Wenn Sie versuchen, denselben Namen in einer anderen Zelle auszuwählen, werden Sie feststellen, dass er nicht mehr zur Verfügung steht.
Tipp: Löschen oder überschreiben Sie keine der Hilfsspalten (Spalten B und C), da diese für die korrekte Aktualisierung der Dropdown-Liste unerlässlich sind. Wenn Sie Ihr Arbeitsblatt übersichtlich halten möchten, ohne die Funktionalität zu beeinträchtigen, blenden Sie diese Spalten einfach aus. Sollten Probleme bei der Aktualisierung der Liste auftreten, prüfen Sie die Formeln auf Bereichsabweichungen oder stellen Sie sicher, dass alle Datenüberprüfungsverknüpfungen korrekt sind und auf den vorgesehenen benannten Bereich verweisen.
Eine Einschränkung dieses Ansatzes liegt darin, dass bei gleichzeitiger Nutzung durch mehrere Benutzer – beispielsweise in einem gemeinsam genutzten Arbeitsblatt – Konflikte entstehen können. Für fortgeschrittenere, skalierbare Lösungen oder um diese Aufgabe mit weniger sichtbarem Arbeitsblattaufwand zu automatisieren, sollten Sie VBA in Betracht ziehen – der folgende Abschnitt stellt diese Alternative vor.
Verwandte Artikel:
Wie fügt man in Excel eine Dropdown-Liste ein?
Wie erstellt man in Excel eine Dropdown-Liste mit Bildern?
Beste Office-Produktivitätstools
Verbessern Sie Ihre Excel-Kenntnisse mit Kutools für Excel und erleben Sie Effizienz wie nie zuvor.Kutools für Excel bietet über 300 erweiterte Funktionen zur Steigerung der Produktivität und Zeit sparen.Klicken Sie hier, um die Funktion zu erhalten, die Sie am dringendsten benötigen...
Office Tab bringt eine tabbasierte Oberfläche in Office und macht Ihre Arbeit viel einfacher
- Aktivieren Sie tabbasiertes Bearbeiten und Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters – statt jedes in einem separaten Fenster zu öffnen.
- Steigert Ihre Produktivität um 50 % und erspart Ihnen täglich Hunderte von Mausklicks!
Alle Kutools-Add-Ins – ein Installationsprogramm
Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook und PowerPoint sowie Office Tab Pro – ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- Alles-in-einem-Paket— Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro
- Ein Installationsprogramm, eine Lizenz— innerhalb weniger Minuten eingerichtet (MSI-fähig)
- Funktioniert besser zusammen— optimierte Produktivität über alle Office-Anwendungen hinweg
- 30-tägige Vollversion zum Testen— keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis— sparen Sie im Vergleich zum Kauf einzelner Add-Ins