Beitrag aus SmartTools Excel Weekly
Dropdownlisten aus dynamischen Arrays
Excel 365
FRAGE Ich habe mit dynamischen Arrayfunktionen experimentiert und entdeckt, wie schnell sich damit duplikatfreie Auflistungen von Spalteneinträgen erstellen lassen. Eine Formel wie =SORTIEREN(EINDEUTIG(C5:C53)) liefert zum Beispiel alle unterscheidbaren Einträge aus dem Bereich C5:C53 und sortiert diese Auflistung außerdem noch. Das Formelergebnis möchte ich jetzt für eine Dropdownliste verwenden, die ich mit Hilfe des Befehls Daten-Datentools-Datenüberprüfung anlege. Das Problem: Wenn ich den Ergebnisbereich der erwähnten Formel (zum Beispiel F5:F22) als Quelle der Dropdownliste eintrage, passt Excel den Quellbereich nicht automatisch an, falls neue Spalteneinträge hinzukommen und sich der Ergebnisbereich der Formel etwa über den Bereich F5:F24 erstreckt. Lassen sich dynamische Arrays nicht als Quelle einer Dropdownliste einsetzen?
B. Dreher
ANTWORT Dynamische Arrays lassen sich durchaus als Quelle einer Dropdownliste einsetzen. Sie müssen dabei aber beachten, dass die Arrays aus einer Formelzelle und einem variablen Überlaufbereich bestehen.
Den Überlaufbereich umgibt Excel mit einem blauen Rahmen. Außerdem ist die Formel, mit der Sie das dynamische Array bilden, nur in der Eingabezelle aktiv. In allen anderen Zellen des Überlaufbereichs ist die Formel in der Bearbeitungszeile ausgegraut, und sie lässt sich dort nicht bearbeiten.
Dieses Verhalten beeinflusst auch den Einsatz als Dropdownlistenquelle. Als Adresse müssen Sie allein die Formelzelle verwenden. Und damit Excel weiß, dass Sie nicht nur explizit diese eine Zelle meinen, sondern den gesamten Überlaufbereich, fügen Sie der Zelladresse das #-Zeichen hinzu.
Wenn Sie – wie in Ihrem Beispiel – die dynamische Arrayformel
=SORTIEREN(EINDEUTIG(C5:C53))
in Zelle F5 eingegeben haben, erstellen Sie aus dem Überlaufbereich also folgendermaßen eine Dropdownliste:
=F5#
Nun können Sie per Dropdownliste einen Eintrag aus dem dynamischen Array auswählen. Und wenn sich das Ergebnis des Arrays ändert, das heißt, falls der Überlaufbereich größer oder kleiner wird, passt sich automatisch die Auswahl in der Dropdownliste an.
