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
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
Im folgenden Fenster wähle ich unter „Zulassen“ die Option „Liste“ und gebe im Feld „Quelle“ die dargestellte Formel ein:
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 Parameter „-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
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
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
Und somit habe wir mit Hilfe der Funktion BEREICH.VERSCHIEBEN() eine wirklich dynamische Drop-Down-Liste erstellt.
Jetzt als E-Book (im PDF-Format) zum Download. Hier erfährst du mehr dazu…
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.
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
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
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?
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
Pingback: Die Qual der Wahl: Mehrspaltige Dropdown-Listen | Der Tabellen-Experte
Hallo Martin,
echt ne super Seite die da hast. Mach bitte weiter so. 🙂
Hallo Christian,
vielen Dank für das Lob und weiterhin noch viel Spaß auf der Seite hier!
Schöne Grüße,
Martin
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?!
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
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
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
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.
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–
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
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 🙂
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 🙂
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.
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?
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
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–
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!
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
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
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
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
Super Gut muss ich ja mal sagen als Hans hasn
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
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
Vielen Dank Martin.
Hab das mit der Bennenung hinbekommen 😉
Ich habe diese dynamische Drop-Down-Liste mit den zurückliegenden zehn Einträgen ausprobiert und dabei festgestellt, dass maximal acht Einträge in der Liste angezeigt werden. Bei mehr als acht Einträgen wird am rechten Rand der Combo-Box ein Scrollbalken eingefügt, mit dessen Hilfe ich die weiteren Einträge anzeigen (und auswählen) kann.
Ist die Höhe dieser Drop-Down-Liste irgendwie veränderbar?
Herzlichen Neujahrsgruß
Jörg
Hallo Jörg,
auch Dir ein gutes Neues Jahr! Die Anzahl der Einträge in einer normalen Dropdownliste kann leider nicht verändert werden. Die 8 Einträge sind Excel-Standard. Wenn das ein wichtiges Kriterium für Dich ist, musst Du auf die Formularsteuerelemente in den Entwicklertools zurückgreifen. Da lässt sich so etwas einstellen.
Schöne Grüße,
Martin
Hallo,
Ich habe eine Frage und zwar ist es möglich wenn ich eine dynamische Drop down Liste ausgefüllt habe den Inhalt in eine Grafik zu übernehmen, automatisch ? Also dass ich wenn ich nach dem übernehmen des Inhalts die Drop down Liste verändere er das in die zeile unter das übernommene einträgt ?
Ist das verständlich? :S..
Ich würde mich über Hilfe sehr freuen.
Grüße
Nin
Hallo Nini,
wenn ich es richtig verstanden habe, hast Du in einer Liste Einträge, aus denen Du ein Diagramm machen möchtest. Und wenn Du neue Einträge in der Liste ergänzt, sollen die auch im Diagramm automatisch berücksichtigt werden. Richtig?
So etwas ist möglich. Dazu musst Du nur die Datenbereich für das Diagramm dynamisch gestalten. Am einfachsten geht das, indem Du die Quelltabelle in eine formatierte Tabelle umwandelst (Menü Start | Als Tabelle formatieren) und dann darauf das Diagramm aufbaust. Formatierte Tabellen „wachsen“ automatisch mit und damit wächst auch ein Diagramm mit, dass sich auf eine solche Tabelle bezieht.
Schöne Grüße,
Martin
Fast, ich würde gerne die 5-6 aufeinander folgenden Dropdownlisten ausfüllen und dann irgendwie in eine Tabelle auf einem anderen tabellenblatt übertragen. Und wenn ich dann wieder die Drop down verändere darf sich die Tabelle auf dem ändern Blatt nicht verändern… ich könnte die Teile wenn ich sie ausgefüllt habe immer mit Copy und paar übertragen und dann alles wieder löschen aber das finde ich sehr umständlich:S
LG
Nini
Hallo Nini,
das ist jetzt doch nochmal etwas anderes, als ursprünglich beschrieben. So etwas ist mit „normalen“ Mitteln nicht möglich, dazu wäre VBA-Programmierung nötig.
Schöne Grüße,
Martin
Pingback: Jetzt kommt Bewegung rein: Dynamische Bereichsnamen | Der Tabellen-Experte
Hallo Martin,
eine tolle Seite mit vielen super tollen Tipps, die mir schon sehr geholfen haben.
Vor allem die beiden Texte über die dynamischen Drop-Down-Listen sind super und einfach nachzuvollziehen.
Aktuell stehe ich aber vor einem Problem, zu dem ich irgendwie keine Lösung finde:
Gibt es eine Möglichkeit, dass der Datenbezug der Drop-Down-Listen auf eine externe Excel-Tabelle verweist, also nicht in der gleichen Arbeitsmappe liegt?
Hintergrund:
Jeder Kollege arbeitet mit einer eigenen Tabelle, sie greifen aber alle auf den gleichen Datenbestand zu. Wenn sich dieser ändert, muss ich die Daten in jeder einzelnen Tabelle manuell ändern… Dies ist natürlich anfällig für Fehler. Wenn eine automatische Verknüpfung hier möglich wäre, müsste nur einmal zentral geändert werden und alle arbeiten mit den gleichen Daten.
Ich habe es z.B. versucht mit „Externe Daten abrufen“ mittels MS Query. Habe dann zwar meine Daten in der Arbeitsmappe, allerdings werden meine vorher in den Daten so sorgsam angelegten einzelnen Bereiche und Tabellen zu einer einzigen großen Tabelle zusammengefasst… Damit funktioniert die dynamische Drop-Down-Liste nicht mehr, da sie sich ja auf einen Tabellennamen bezieht
Gibt es noch andere Möglichkeiten?
Vielen Dank und schöne Grüße,
Karen
Ganz vergessen – wir arbeiten mit Excel 2010
Hallo Karen,
ich fürchte, hier sind dann tatsächlich irgendwo die Grenzen der Dynamik erreicht: Dynamische Dropdown-Listen, die dann auch noch externe Quellen einbeziehen wird vermutlich nicht funktionieren. Zumindest ist mir keine Lösung dafür bekannt.
Tut mir leid,
Martin
Hallo Martin,
schade, war meine große Hoffnung, um mir das Leben ein bisschen einfacher zu machen.
Trotzdem vielen Dank – und mach weiter so mit deiner Seite, die ist echt super!!!
Karen
Hallo Martin,
zunächst vielen vielen Dank für die sehr verständlichen Anleitungen und Erklärungen.
Ich möchte ein dynamisches Dropdown-Menü erstellen, dabei aber nicht wie von dir gezeigt eine vorher definierte Tabelle nutzen, sondern gerne eine eindimensionale Matrix, die ich über den Namensmanager definiert habe. Ist das möglich?
Beste Grüße
Tobias
Nachtrag:
Wenn ich direkt den Namen der Matrix eingebe funktioniert das Dropdown-Menü, aber ich möchte die Wahl der Matrix von dem Wert eines vorher gewählten Dropdowns abhängig machen und daran scheiter ich noch.
Lösung:
Mit der Index-Funktion kann ich Excel sagen, dass es die in dem vorgeschalteten Dropdown definierte Bezeichnung als Namen des Vektors verwenden soll.
Hallo Tobias,
freut mich zu hören, dass Du das Problem noch selbst lösen konntest.
Schöne Grüße,
Martin
Hallo Martin, vielen Dank für die hilfreichen Beiträge. Ich habe bereits fast alles hinbekommen, was ich in meiner dynamischen DD-Liste wollte. Das Einzige, was mir jetzt noch Fragen aufwirft ist das: ich habe 3 Spalten, die Dropdown enthalten. Angenommen Land – Stadt – Bezirk. Wenn ich in einer Zeile eine Auswahl in allen drei Spalten treffe und dann in der ersten Spalte das Land wechsle; wie bekomme ich es hin, dass dann automatisch die nachfolgenden Zeilen keine Werte mehr enthalten oder eingefärbt werden. Sodass klar ist, dass hier noch ein Fehler vorliegt?
Ich hatte in einem anderen Kommentar etwas von „bedingter Formatierung“ gelesen. Das bekomme ich aber irgendwie nicht hin.
Viele Grüße Julia
Hallo Julia,
die Lösung führt tatsächlich über eine bedingte Formatierung. Das ist allerdings nicht mit ein zwei Sätzen erklärt, aber ich habe verschiedene Möglichkeiten dazu in meinem E-Book „Dropdown-Listen mit Excel“ beschrieben (ich möchte es dir nicht „aufschwatzen“, aber vielleicht lohnt sich die kleine Investition ja für dich)
Schöne Grüße,
Martin
Hallo Martin!
Super Erklärung! Vor allem so dass es „jeder“ versteht.
Ich würde Dir gern ab und zu ganz unverbindlich Fragen per Email stellen. Würde mich sehr freuen wenn Du mir eine Email schreibst so dass ich auch Deine Adresse bekomme.
Alternativ könnte ich meine Fragen auch z.B. hier oder wo anders auf der Seite stellen. Dann wäre es nett wenn Du mir beschreibst wo, und wie ich über Antworten benachrichtigt werden kann.
Kannst Du ein Forum empfehlen wo ich auf meine Fragen auch solche Antworten bekomme die ich verstehe, wie Deine Beschreibung hier! ?
Hallo Dave,
wenn es sich um Fragen handelt, die sich auf einen hier veröffentlichten Blogartikel beziehen, kannst du sie gerne in den Kommentaren unterhalb des betreffenden Artikels stellen. Darüberhinaus leiste ich keinen (kostenlosen) Excel-Support. Sollte es sich um eine geschäftliche Anfrage handeln, steht dafür jedoch immer das Kontaktformular zur Verfügung:
https://www.tabellenexperte.de/kontakt/
Mehr kann und möchte ich nicht anbieten.
Schöne Grüße,
Martin
Hallo,
ich finde die Lösung sehr interessant und danke für die ausführliche Erklärung.
Ich versuche gerade mehrere DropDown-Listen über VBA in Excel umzusetzen, komme jedoch nicht weiter.
Hier bekomme ich sogar einen Laufzeitfehler „1004“, wenn die oben erklärte Variante aufzeichne und abspielen will.
Range(„D14″).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=“=Bereich.Verschieben(Kundenübersicht!$B$3;0;1;1;5)“
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = „“
.ErrorTitle = „“
.InputMessage = „“
.ErrorMessage = „“
.ShowInput = True
.ShowError = True
End With
Kenn Sie sich hiermit auch aus?
Aufgrund des ständigen wandels der Rohdatentabelle, will ich die Werte als Variablen ändern und da scheint mir diese Variante von Ihnen sehr sinnvoll und „theoritsch“ einfach umsetzbar.
Vielen Dank im Voraus.
Hallo Adrien,
hier muss ich Sie leider enttäuschen: Zu VBA gebe ich hier auf dem Blog keine Tipps.
Schöne Grüße,
Martin