Dynamische Drop-Down-Listen (Teil 2) 29

Mit BEREICH.VERSCHIEBEN nicht nur die Drop-Down-Liste, sondern auch deren einzelne Elemente dynamisch anpassen.
 

Letzte Woche habe ich eine Möglichkeit gezeigt, wie man Drop-Down-Listen in Excel von Einträgen in anderen Zellen abhängig machen kann.

Die Auswahlliste verändert sich somit zwar dynamisch, die einzelnen Listenelemente an sich sind aber in diesem Fall trotzdem statisch. Es gibt aber auch Anwendungsfälle, wo sich die einzelnen Elemente permanent verändern sollen und somit richtig dynamisch werden.

Dazu setzen wir die Funktion BEREICH.VERSCHIEBEN() ein.

Als heutiges Beispiel dient mir ein kleines Haushaltsbuch, in dem ich meine Ausgaben erfassen möchte. Um mir die Eingabe zu erleichtern, sollen mir die jeweils letzten 5 verwendeten Ausgabebeschreibungen als Eingabehilfe in einer Drop-Down-Liste angeboten werden.

Und so geht’s:

Datenüberprüfung

Meine Beispieltabelle sieht folgendermaßen aus:

Beispieltabelle

Beispieltabelle

Für die Spalte B mit den Beschreibungen möchte ich nun eine dynamische Drop-Down-Liste erstellen. Daher markiere ich ab Zelle B16 einige weitere Zellen und rufe die Funktion “Datenüberprüfung” auf:

Drop-Down-Liste erstellen

Drop-Down-Liste erstellen

Im folgenden Fenster wähle ich unter “Zulassen” die Option “Liste” und gebe im Feld “Quelle” die dargestellte Formel ein:

Datenüberprüfung

Datenüberprüfung

BEREICH.VERSCHIEBEN

Was bewirkt nun BEREICH.VERSCHIEBEN()? Es handelt sich dabei wieder um eine sogenannte Matrix-Funktion.

Allgemein gesprochen gibt diese Funktion einen Zellenbezug aus, der von meiner Ursprungszelle um eine bestimmte Anzahl von Zeilen und Spalten verschoben ist. Dabei kann ich bei Bedarf auch gleich noch die Höhe und Breite des neuen Zellbezugs festlegen:
=BEREICH.VERSCHIEBEN(Ursprung; Zeilen; Spalten; Höhe; Breite)

Die Ursprungszelle ist in meinem Beispiel die B16, da ich ja hier die neue Beschreibung eingeben möchte. Somit ist dies mein erster Parameter in der Funktion:
=BEREICH.VERSCHIEBEN(B16; -5; 0; 5)
Da ich die 5 darüber liegenden Elemente ausgeben möchte, gebe ich als zweiten Paramter “-5” an. Positive Werte bedeuten nach unten verschobene Zeilen, negative Werte nach oben verschobene. Das heißt, die Startposition ist in unserer Formel um 5 Zeilen nach oben verschoben.

Meine gesuchten Werte stehen ebenfalls in Spalte B, die Spalte soll also nicht verschoben werden. Deshalb lautet der dritte Parameter “0” (Null):
=BEREICH.VERSCHIEBEN(B16; -5; 0; 5)

Fehlt noch die Höhe und Breite des auszugebenden Bereichs: Die Drop-Down-Liste soll 5 Werte anzeigen, somit ist der vierte Parameter “5”. Die Breite kann ich hier auch weglassen; Excel nimmt dann die gleiche Breite wie meinen Ursprung, also 1 Spalte. Alternativ hätte ich auch den Wert “1” angeben können:
=BEREICH.VERSCHIEBEN(B16; -5; 0; 5; 1)

Wichtig: Da ich natürlich auch andere Werte als die letzten 5 eingeben möchte, muss ich die Fehlerprüfung abschalten:

Fehlermeldung abschalten

Fehlermeldung abschalten

Andernfalls würde Excel jeden nicht in der Drop-Down-Liste enthaltenen Wert mit einer Fehlermeldung blockieren.

Und so sieht das Ergebnis aus:

Ergebnis: Die letzten 5 Werte

Ergebnis: Die letzten 5 Werte

Die Drop-Down-Liste bietet mir die letzten 5 Werte aus meiner Bezeichnungsspalte an. Und dank der abgeschalteten Fehlerprüfung kann ich trotzdem noch andere Einträge eingeben, wie man im folgenden Screenshot sieht. Die Elemente der Drop-Down-Liste werden automatisch angepasst:

Dynamisch angepasste Werte

Dynamisch angepasste Werte

Und somit habe wir mit Hilfe der Funktion BEREICH.VERSCHIEBEN() eine wirklich dynamische Drop-Down-Liste erstellt.

Das könnte dich auch interessieren:

Und immer daran denken: Excel beißt nicht!

P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)

P.P.S. Das Problem sitzt meistens vor dem Computer.



Kommentar erstellen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

29 Gedanken zu “Dynamische Drop-Down-Listen (Teil 2)

  • Nikolai

    Hallo Martin!

    Toll erklärt. Wie immer 😉 Nach den zwei Artikeln jetzt hab ein leichtes Gefühl von: “aaah, wenn ich das früher gewusst hätte”.

    Ich hätte mir in den vergangenen Jahren einigen Ärger mit Dropdowns erspart! 😉

    Lieben Gruß,
    Nikolai

    • Martin Beitragsautor

      Hallo Nikolai,

      dankeschön, freut mich sehr, dass Dir die Artikel gefallen haben. Und wenn es Dich beruhigt: Mir geht es oftmals genauso: “hätte ich das nur früher gewusst…”

      Schöne Grüße,
      Martin

      • Benjamin

        Hallo Martin,

        Ich habe deine Beiträge zum DropDown gelesen. Wirklich sehr Interessant dies zu wissen, allerdings war ich eigentlich auf der Suche nach etwas anderem.

        Ich möchte, dass die meine Ausgewählten Einträge dynamisch mit verändern, wenn ich meine Quelle verändere.

        wenn ich das Beispiel mit deinen Autohersteller heranziehe.
        hier werden die Begriffe “AUDI, BMW, Mercedes und Volkswagen” verwendet.

        nun haben ich für 100 Felder aus den oben genannten 4 Möglichkeiten ausgewählt.
        zu einem Zeitpunkt x Ändert sich aber die Bezeichnung “Mercedes” zu “MB” gibt es eine Möglichkeit dass die bereits ausgewählten dann dynamisch anpassen?

        • Martin Weiß Beitragsautor

          Hallo Benjamin,

          die Listeneinträge können sich schon dynamisch anpassen. Das Problem ist nur, dass Werte, die in einer Zelle bereits ausgewählt und eingetragen wurden, sich nicht so ohne weiteres dynamisch verändern können. Woher soll Excel auch wissen, welchen Wert man in einer Zelle haben möchte, wenn sich im Vorgängerfeld z.B. der Hersteller geändert hat.

          Es ist aber möglich, z.B. mit Hilfe von bedingter Formatierung solche Änderungen hervorzuheben. Das ist übrigens auch eines der Themen, die in meinem Dropdown-Leitfaden behandelt werden.

          Schöne Grüße,
          Martin

    • Martin Weiß Beitragsautor

      Hallo Christian,

      vielen Dank für das Lob und weiterhin noch viel Spaß auf der Seite hier!

      Schöne Grüße,
      Martin

  • Jessica

    Hallo,

    super erklärt. Auf anhieb hinbekommen.
    Leider kann ich diese Datei nun nicht für andere Nutzer freigeben, da es mit einer Tabelle verbunden ist 🙁
    Umgehen kann man das nicht?!

    • Martin Weiß Beitragsautor

      Hallo Jessica,

      es ist tatsächlich so, dass in freigegebenen Arbeitsmappen bestimmte Funktionen gar nicht oder nur in eingeschränkter Form zur Verfügung stehen. Dazu gehören leider auch formatierte Tabellen. Diese müssen vorher in einen normalen Bereich umgewandelt werden und das lässt sich leider auch nicht umgehen.

      Schöne Grüße,
      Martin

  • Timo

    Hallo Martin,

    danke für die Erklärung.

    Jetzt habe ich das Thema “Bereich.Verschieben” besser verstanden.

    Leider funktioniert das nicht bei mir.

    Folgende Situation:

    Zwei voneinander abhängige Dropdown-Menüs sollen ihren Input aus dynamischen Namensbereichen beziehen.

    A1 – Überschrift: Land A2=Dropdown für Länderauswahl
    B1 – Überschrift: Stadt B2=Dropdown für Stadtauswahl

    Beim erstellen des zweiten Dropdown für Stadtauswahl (B2) wird als Quelle =INDIREKT(A2) eingetragen.

    In Zeile 4 stehen einige Länder:
    A4=Deutschland, B4=England, C4=Frankreich
    Die 3 Länder werden ausgewählt. Im Namenmanager wird der Name “Land” vergeben.

    Ab Zeile 5 werden je Spalte die zu den Ländern passenden Stadtnamen vergeben.

    A5: Berlin A6:München A7: Düsseldorf
    B5: London B6: …. usw.

    Danach habe ich wieder im Namensmanager die Namen der Städte dynamisch angelegt.

    1. Schritt: Name für die deutschen Städte (A5:A7) lautet Deutschland (A4).
    2. Bereich dynamisch anlegen: Das läuft ja wie in deinem gut erläuterten Beispiel.
    =bereich.verschieben(A5;;;z1;)

    Bei Z1 habe ich mir für Deutschland eine Hilfsformel gebastelt, die alle Einträge ab A5 zählt. In dem Beispiel stehen bei Deutschland 3 Städte, also Z1=3.

    Demnach wird durch BEREICH.VERSCHIEBEN der Bereich A5:A7 für den Bereichsnamen “Deutschland” vergeben.

    Bis hierhin alles wunderbar.

    Das erste Dropdown bei A2 (Auswahl der Stadt) funktioniert ohne Probleme.

    Doch das zweite Dropdown bei B2 bleibt leer.

    Was mache ich falsch?

    Mit freundlichen Grüßen

    Timo

    • Martin Weiß Beitragsautor

      Hallo Timo,

      das ist wirklich eine kniffelige Angelegenheit, auf die ich tatsächlich auch keine gute Antwort weiß. Offensichtlich hat Excel bei der Datenüberprüfung ein Problem, wenn es wie hier mehrmals um die Ecke geht.

      Die einzige Möglichkeit, die ich gefunden habe, führt über formatierte Tabellen, die man dann nach den Ländern benennt (Deutschland, England, Frankreich usw). Das hatte ich im ersten Artikel dieser Serie beschrieben. Damit vermeidest Du das Konstrukt mit BEREICH.VERSCHIEBEN und hast trotzdem eine Dynamik bei der Länge der einzelnen Dropdownlisten für die Städte.

      Vielleicht hat ja einer der Leser noch eine andere Idee.

      Grüße,
      Martin

    • --Fragezeichen--

      Der erste Dropdown “A2” sollte doch Auswahl Land sein und “B2” für die Stadt?
      Mal davon abgesehen, ist oft bei solchen Problemen bei mir zumindest, die Schreibweise nicht identisch (Leerzeichen beachten).
      Also Kontrolle im Namensmanager und in den Zellen, die Bezeichnungen müssen für den “indirekt” Verweis absolut identisch sein.
      Der “Bereich” im Namensmanager muss sich auch auf die gleiche Arbeitsmappe beziehen.

  • --Fragezeichen--

    Hallo Martin,
    ganz vergessen Deine Seiten zu loben.
    Also ganz super erklärt 🙂 vielen Dank.
    War auf der Suche nach einem Dynamischen “Listen Dropdown”, wenn in der Liste etwas in eine neue Zeile geschrieben wird, dass das in den bisherigen Dropdown automatisch als Erweiterung aufgenommen wird und nicht als eine dynamische Verschiebung. Habe es aber prinzipiell mit einer Variablen gelöst, war nicht so schwer dann.
    Das eigentliche “Problem” was ich dennoch dann mit dem Excel habe, ist dass diese Dropdown Listen nur in der Tabelle funktionieren in der die Liste auch steht.
    Bei der Quelle in der Datenprüfung, oder in dem Namensmanager geht leider kein Verweis auf eine andere Tabelle. Zumindest habe ich das noch nicht geschafft (Fehlermeldung: “Sie können keine Bezüge auf andere Tabellen oder Arbeitsmappen in Datenprüfung-Kriterien nicht verwenden”).
    Würde z.B.: das in der Quelle benötigen: „=BEREICH.VERSCHIEBEN(Listen!X6;0;0; Listen!$AA$6)“,
    wobei AA6 die Formel =ANZAHL2(X6:X65000) enhält.
    Leider würde ich genau das benötigen, damit ich nicht immer die Länge des Dropdowns “per Hand” nachpflegen muss (in Diversen Dropdowns und im Namensmanager). Gibt es da eine ander Möglichkeit das zu realisieren, ohne dass in den “Dropdowns” dann die vielen Leerzeilen erscheinen, wenn die Liste zu lang ist?
    Es ist unmöglich die Listen mit der Matrix A:A zu fixieren, weil z.B. die Zeile 1-5 als Überschrift dient und nicht in dem Dropdown vorkommen soll. Auch ein um verweisen auf eine “leere” Tabelle würde den Rahmen der notwendigen Tabellen sprengen (habe über 80 Listen unterschiedlicher, dynamischer Länge).
    Daher auch erheblicher Aufwand, wenn bei den Listen etwas angefügt wird.
    Da wird immer eine Administration benötigt um die Zeilen anzugleichen, die ich gerne damit eliminieren würde.
    Gibts da eine Möglichkeit?
    Beste Grüße
    –Fragezeichen–

    • Martin Weiß Beitragsautor

      Hallo -Fragezeichen-,

      danke, freut mich sehr, wenn Dir die Seite gefällt!

      Wenn Du in Excel keine Dropdown-Listen auf einem anderen Blatt verwenden kannst, dann setzt Du vermutlich Excel 2007 ein (oder älter), richtig? Ab Excel 2010 ist das nämlich ohne Probleme möglich. Die gute Nachricht: Mit einem einfachen Trick geht es auch für die 2007er-Version:

      Du musst nur für den Bereich, der die Dropdown-Werte enthält, einen Namen vergeben. Und diesen Namen verwendest Du dann in der Datenüberprüfung. Das gleiche kannst Du auch für Funktionen machen. Lege also im Namensmanager einen neuen Namen an (z.B. “Dropdown”) und gib als Quelle nicht direkt den Zellbereich an, sondern Deine Funktion =BEREICH.VERSCHIEBEN(Listen!$X$6;0;0; Listen!$AA$6). Achte dabei, dass auch der erste Parameter einen absoluten Zellenbezug enthält. Und dann verwende in der Datenüberprüfung einfach den Namen =Dropdown.

      Grüße,
      Martin

      Grüße,
      Martin

  • --Fragezeichen--

    Hallo Martin,

    Du bist mein HELD 🙂 genau so ist es 🙂 die Listen sind ja alle schon im Namensmanager, muss nur noch das =Bereich.Verschieben() einfügen.
    It saves my Jear 🙂

    • --Fragezeichen--

      Leider funktioniert es doch nicht habe, die Listen verwechselt gehabt 🙁 sorry (Excel 2007; war zu Euphorisch)
      Im Namens-Manager bleibt der Bereich des Inhaltes auch leer, also kein Inhalt im Manager selber wird angezeigt, sonst sehe ich was die Liste hat).
      Aber die Fehlermeldung bleibt aus, dass diese Listenverweise nicht gehen.
      Werde es @home wohl in 2013 mal testen. Die Firma hat noch 2007.
      Aber dennoch nun habe ich die Begründung und brauche nicht mehr weiter nachzudenken, wie es noch gehen könnte. Brauche eben den Versionswechsel.
      Also echt vielen Dank für die Info 🙂

      • --Fragezeichen--

        Update:
        Es funktioniert mit den Listen schon.
        Funktionieren tut es bei mir nicht, wenn man diese Dropdown Listen dann mit “indirekt” “Quelle: =indirekt(Dropdown1)” aufrufen möchte.

        • --Fragezeichen--

          Update 2:
          leider funktioniert das bei mir auch nicht mit Excel 2013 🙁
          Indirekt mit Dropdowns geht nicht
          ohne indirekt geht es, wenn in der Quelle dann =Dropdown1 steht
          Gibts da evtl. einen anderen Weg das zu realisieren?

          • Martin Weiß Beitragsautor

            Hallo -Fragezeichen-,

            der einzige Weg, den ich noch sehe ist, dass Du den Bereich für die Dropdown-Liste nicht dynamisch per BEREICH.VERSCHIEBEN festlegst, sondern einen Namen für einen festen Zellenbereich angibst. Dann klappt auch die INDIREKT-Funktion in der Datenüberprüfung. INDIREKT in Kombination mit einem dynamisch ermittelten Bereich scheint tatsächlich nicht zu funktionieren.

            Grüße,
            Martin

  • --Fragezeichen--

    Hallo Martin,
    vielen Dank für die Information.
    Habe es ja bisher nur “nicht dynamisch” in den Dropdown Listen realisieren können.
    Ist eben doch sehr viel Arbeit alle Listen (ca. 80) “per Hand” immer anzupassen, wenn neue Einträge erfolgen.
    Daher war ich auch so begeistert, dass es so augenscheinlich doch gehen sollte.
    Aber Versionsunabhängig geht die Funktion “Indirekt” nicht mit dynamischen Funktionen bzw. den Dropdown Listen (bzw. Tabellenspalten).
    Hatte auch schon probiert eine als Tabelle definierte “Tabellenspalte” dynamisch zu definieren, aber auch das scheitert schon an der Eingabe, die aus dem “bereich.verschieben” sofort den aktuellen Bereich errechnet (Die Tabellenspalte wird automatisch durch Formeln erzeugt). Sonst hätte ich ja dann auf diese “Statische” Spalte verweisen können.
    Brauche also wohl eine Funktion, die mir aus einer automatisch und dynamisch entstehenden Liste eine statische Liste macht, die dann in obigen Sinne verwendbar sein kann (wobei dann im Dropdown keine Leerzeilen sind, weil das kann ich schon 😉 ).

    Grüße
    –Fragezeichen–

  • Kathrin

    Hallo Martin,

    erst einmal: Tolle Erklärungen, das hat mir sehr geholfen!

    Ich habe noch eine Frage zu meiner Datei.
    Ich habe nun folgende Zeilen:
    1. Transportart (normale Dropdown aus Liste, Optionen FCL & LCL)
    2. Gewicht in kg (freies Feld)
    3. Größe des Containers (normale Dropdown aus Liste, Optionen 20′ & 40′).

    Ist es möglich die Containergrößen-Dropdown (3.) automatisch mit einer leeren Zeile zu versehen, sobald bei der Transportart (1.) LCL angegeben wird? Sprich selbst wenn ich in Zeile 3 für die Containergröße schon 20′ ausgewählt habe, springt diese Zeile auf eine Leerzeile sobald LCL in Feld 1 ausgewählt wird.
    Ich habe mal versucht eine Wenn-Formel in dem Quellenbereich anzugeben, aber das hat leider nicht funktioniert. (Formel die ich probiert habe. =WENN([1.]=”FCL”;INDIREKT([1.]);””)

    Ich hoffe ich habe mich verständlich ausgedrückt!

    • Martin Weiß Beitragsautor

      Hallo Kathrin,

      freut mich, wenn der Artikel geholfen hat. Was Deine Frage angeht:
      Ohne VBA-Programmierung ist es meines Erachtens nicht möglich, eine bereits eingetragene Containergröße durch einen leeren Eintrag zu ersetzen, sobald man eine andere Transportart ausgewählt hat. Das Problem dabei ist, dass die Zelle selbst ja keine Formel enthält, die dies prüft. Lediglich die Logik in der Dropdown-Liste könnte eine Prüfung enthalten, die aber nicht mehr greift, sobald ein Wert einmal ausgewählt wurde und in der Zelle steht.

      Es wäre aber möglich, über eine bedingte Formatierung zumindest das Feld mit der Containergröße einzufärben oder aber die Schriftart z.B. auf weiß zu ändern, wenn eine andere Transportart gewählt wurde. Vielleicht hilft das ja weiter.

      Grüße,
      Martin

  • Phine

    Hallo,
    ich möchte gerne folgende Dropdownliste erstellen und habe da ein paar Probleme: Ich habe eine Tabelle, in deren erster Spalte die Arbeiter stehen, in den folgenden Spalten stehen in den jeweiligen Zeilen die Arbeitseinsätze. Ich möchte nun in einer anderen Tabelle eine dropdownliste erstellen, einmal die Auswahl der Kalenderwoche und dann die Auswahl von verfügbaren Arbeitern und verfügbarem Ersatz. Ist so etwas möglich?
    Danke schon mal im voraus.

    Beste Grüße

    Phine

    • Martin Weiß Beitragsautor

      Hallo Phine,

      möglich ist bei Excel fast alles 🙂
      Dynamische Dropdown-Listen sind aber immer eine sehr spezielle Angelegenheit und sehr vom konkreten Einzelfall abhängig, so dass man keine allgemein gültigen Aussagen treffen kann. Daher kann ich Dir hier keinen konkreten Tipp geben.

      Schöne Grüße,
      Martin

  • kreylo

    Vielen Dank dir! Ich hüpfe öfters zwischen verschiedensten Excel-Websites hin und her, aber auf deiner verstehe ich vieles viel rascher und schneller. Die dynamischen DropDown-Listen sind auf der Arbeit auf jeden Fall ein Hit geworden :-D..was mich natürlich auch freut. Vielen Dank und mach’ weiter so, kreylo

  • Judith

    Hallo Martin,

    Deine Erklärungen und bildlichen Hilfestellungen sind Top!

    Ich versuche gerade eine Dynamische Drop-Down-Liste zu erstellen wobei ich mit mehreren Tabellenblättern arbeiten möchte.

    Es geht um eine Jahresliste, also monatlich aufgeführt, sprich 12 Blätter plus die Liste, die ich separat anlegen möchte, welche dann der Bezugspunkt der Auswahlkriterien sein soll.

    Ist es nicht möglich die Daten aus einer anderen Tabelle zu beziehen?
    Müssen die Daten für eine Überprüfung denn unbedingt vom selben Blatt sein?

    Wenn es nicht möglich ist, wie kann ich dann vorgehen?

    Ich bin halt doch ein Laie und benötige viel Hilfe! 😉

    Vielen Dank Judith

    • Martin Weiß Beitragsautor

      Hallo Judith,

      es ist grundsätzlich kein Problem, dass sich diese Daten auf einem anderen Arbeitsblatt befinden. Ab Excel 2010 (und neuer) kann man direkt in der Datenüberprüfung im Eingabefeld auf das andere Blatt verweisen. Wenn Du noch mit Excel 2007 arbeitest, musst Du für den Bereich mit den Quelldaten zuerst einen Namen vergeben und dann in der Datenüberprüfung diesen Namen als Quelle angeben.

      Schöne Grüße,
      Martin