Ein kleiner Dauerbrenner in Excel sind Dropdown-Listen: die praktischen kleinen Auswahlfelder, über die man die Dateneingabe komfortabel gestalten und auf zulässige Werte beschränken kann.
Heute zeige ich dir einen Weg, wie man die Einträge solcher Listen (fast) automatisch sortiert, erweitert und um Duplikate bereinigt anzeigen lassen kann, wenn sich die zugrunde liegende Datenquelle dynamisch verändert.
Und so geht’s:
Beispieldatei herunterladen
Das Szenario
Meine Beispieldatei enthält eine Adressenliste, in der laufend neue Datensätze dazukommen oder nicht mehr benötigte entfernt werden:
Beispiel: Adressenliste
Um die Eingabe etwas komfortabler zu gestalten, soll für den Ort ein Auswahlfeld eingerichtet werden, das alle in der Tabelle vorhandenen Orte anbietet. Dabei sollen folgende Kriterien beachtet werden:
- die Orte in der Auswahlliste sollen alphabetisch sortiert sein und natürlich keine Duplikate enthalten
- wenn in der Adressenliste ein Ort nicht mehr enthalten ist (weil beispielsweise alle Adressen dazu entfernt wurden), soll dieser Ort auch nicht mehr in der Auswahlliste angeboten werden
- die Eingabe von neuen, noch nicht in der Auswahlliste enthaltenen Orten muss möglich sein
- der neue Ort soll anschließend automatisch in der Auswahlliste verfügbar sein
Das sind ziemliche viele Anforderungen, die bei einer „normalen“ Dropdown-Liste erst einmal nicht vorgesehen sind. Aber du bist ja hier, um etwas Neues zu lernen, oder?
Gehen wir es also der Reihe nach an.
Schritt 1: Eine formatierte Tabelle
Falls es sich bei deinen Daten bereits um eine formatierte („intelligente“) Tabelle handelt, kannst du diesen Schritt natürlich überspringen. Ansonsten wandeln wir die Adressenliste erst in eine formatierte Tabelle um:
Start | Als Tabelle formatieren (oder über die Tastenkombination Strg+T)
Du solltest dir grundsätzlich angewöhnen, für formatierte Tabellen einen aussagefähigen Namen festzulegen. Das ist für die Funktionalität zwar nicht zwingend erforderlich, macht aber die Orientierung einfacher, sobald man es mit mehr als nur einer Tabelle zu tun hat. Meine formatierte Tabelle heißt „tblAdressen“:
Die formatierte Tabelle erhält den Namen tblAdressen
Schritt 2: Eine Pivot-Tabelle
Ein nicht unbedingt naheliegender Einsatzbereich für eine Pivot-Tabelle, aber für unsere Zwecke ziemlich praktisch, wie du gleich noch sehen wirst.
Stelle also die aktive Zelle irgendwo in die Adressentabelle und wähle aus dem Menü:
Einfügen | PivotTable
Im sich nun öffnenden Fenster ist als Tabelle/Bereich bereits der Name der Pivot-Tabelle enthalten. Unten im Fenster habe ich noch angegeben, dass die Pivot-Tabelle rechts neben meiner Adressenliste erstellt werden soll:
Eine neue Pivot-Tabelle erstellen
Im „echten“ Leben würde ich sie in einem separaten Blatt erstellen, das man später ausblenden kann. Aber zu Demozwecken ist es besser, wenn man sie direkt im Blick hat.
Nun ziehen wir einfach das Feld „Ort“ in den Zeilenbereich der Pivot-Tabelle und erhalten folgendes Ergebnis:
Ort-Feld in den Zeilenbereich
Wie zu erwarten, wird jeder Ort aus unserer Adressenliste genau einmal angezeigt. Allerdings sind noch ein paar Anpassungen notwendig, damit diese Pivot-Tabelle später als Datenquelle für unser Dropdown-Feld taugt. Wähle in der Feldüberschrift die Option „Von A bis Z sortieren“:
Sortieren der Pivot-Tabelle
Die Pivot-Tabelle ist bei der Erstellung zwar standardmäßig schon sortiert, aber wenn später neue Einträge dazukommen, dann ist das nicht mehr zwingend der Fall. Daher also die explizite Sortierung.
Dann brauchen wir natürlich keine Zeile mit einem Gesamtergebnis:
Entwurf |Gesamtergebnisse | Für Zeilen und Spalten deaktiviert
Gesamtergebnisse werden deaktiviert
Nun rufen wir über einen Rechtsklick auf irgendeinen Eintrag in der Pivot-Tabelle die PivotTable-Optionen auf:
PivotTable-Optionen aufrufen
Hier sind zwei Einstellungen im Registerblatt „Daten“ interessant. Zum einen soll die Pivot-Tabelle automatisch beim Öffnen der Datei aktualisiert werden. Und wir wollen später keine Elemente mehr sehen, die aus der Datenquelle gelöscht wurden. Daher setzen wir die entsprechenden Einstellungen:
Daten-Einstellungen anpassen
Damit sind wir mit der Pivot-Tabelle fertig und können uns um die Datenprüfung kümmern.
Schritt 3: Datenprüfung
Das Dropdown-Feld werden wir gleich über die Datenprüfung einrichten. Zuvor müssen wir aber noch einen Namen festlegen, der immer genau die in der Pivot-Tabelle enthaltenen Zeilen umfasst. Nicht mehr und nicht weniger. Legen wir also einen neuen Namen an:
Formeln | Namen definieren
Als Name habe ich „Dropdown“ gewählt, aber du kannst natürlich jeden anderen Namen verwenden. Wichtig ist jedoch Feld „Bezieht sich auf:“, den hier muss folgende Formel eingetragen werden:
=BEREICH.VERSCHIEBEN(Tabelle1!$H$1;1;0;ANZAHL2(Tabelle1!$H:$H)-1)
Einen dynamischen Namen definieren
Was genau passiert hier?
Die Funktion BEREICH.VERSCHIEBEN ermöglicht es uns, einen dynamischen Bereich festzulegen. Die allgemeine Syntax lautet:
=BEREICH.VERSCHIEBEN(Bezug;Zeilen;Spalten;[Höhe];[Breite])
Der erste Parameter „Bezug“ stellt den Ausgangspunkt dar. In unserem Fall ist das die Kopfzelle unserer Pivot-Tabelle, also H1. Da dieser Bezug fix ist, verwende ich die absolute Schreibweise $H$1 (zusammen mit dem vorausgestellten Blattnamen).
Die beiden nächsten Parameter legen fest, um wie viele Zeilen und Spalten dieser Bezug verschoben werden soll. In unserem Beispiel soll es ja mit dem ersten Ortsnamen losgehen, also eine Zeile unterhalb der Überschrift. Daher der Wert 1. Da wir in der gleichen Spalte bleiben, ist der Wert für den dritten Parameter „Spalte“ 0 (Null).
Und jetzt kommt der eigentlich spannende Teil, nämlich die Höhe. Oder anders gesagt, wie viele Zeilen soll unser Bereich umfassen. Da sich die Pivot-Tabelle im laufenden Betrieb ja dynamisch verändern wird (es kommen neue Orte dazu oder alte fallen weg), zählen wir mit der Funktion ANZAHL2 einfach die Anzahl der Einträge in Spalte H. Und vom Ergebnis ziehen wir 1 ab, da die Überschrift ja nicht mitgezählt werden soll.
Jetzt haben wir also unseren dynamischen Bereich, auf den wir in der Datenprüfung verweisen können. Dazu markieren wir erst die Spalte mit den Orten in unserer Adressentabelle und richten dann die Datenprüfung ein:
Daten | Datenüberprüfung
Datenüberprüfung einrichten
Im Register „Einstellungen“ wählst du im Feld „Zulassen“ den Eintrag „Liste“. Als Quelle gibst du den Namen an, den wir zuvor erstellt haben mit einem vorangestellten Gleichheitszeichen ein (Tipp: Du kannst dir mit der Funktionstaste F3 auch eine Liste aller Namen anzeigen lassen und den gewünschten dort auswählen):
Der definierte Name als Datenquelle
Jetzt wechselst du in das Registerblatt „Fehlermeldung“ und änderst den Typ von „Stopp“ auf „Information“. Das ist notwendig, damit die Eingabe eines neuen Ortes überhaupt möglich wird. Du solltest dann auch einen entsprechenden Hinweis in den Titel und das Feld mit der Fehlermeldung eintragen:
Fehlermeldung anpassen
Und damit können wir unser Auswahlfeld gleich mal testen und einen neuen Datensatz hinzufügen. Zunächst sind in der Dropdown-Liste alle Orte enthalten, die sich in der Pivot-Tabelle befinden:
Dropdown-Liste im Einsatz
Wenn du jetzt aber stattdessen einen ganz neuen Ort eintippst, erscheint unsere zuvor definierte Hinweismeldung, die wir mit OK bestätigen können:
Ein neuer Ort kommt dazu
Damit wird die Eingabe erst einmal akzeptiert, aber in der Dropdown-Liste taucht der Ort trotzdem noch nicht auf. Dazu muss erst die Pivot-Tabelle mit der Tastenkombination „Strg + Alt + F5“ aktualisiert werden (oder über das Menü Daten | Alle aktualisieren). Das ist der einzige Wermutstropfen, den du bei dieser Lösung hast. Allerdings wird aufgrund unserer weiter oben vorgenommenen Einstellung die Pivot-Tabelle und damit auch die Dropdown-Liste zumindest dann automatisch aktualisiert, wenn die Datei das nächste Mal geöffnet wird.
Schritt 5: Ein letzter Feinschliff
Damit man als Anwender wenigstens auf neue Einträge aufmerksam gemacht wird, die sich noch nicht in der Auswahlliste befinden, kann man noch eine bedingte Formatierung einsetzen. Hierfür markieren wir wieder die Spalte mit den Orten und richten eine neue Formatierungsregel ein:
Start | Bedingte Formatierung | Neue Regel
Als Regeltyp wählst du „Formel zur Ermittlung der zu formatierenden Zellen verwenden aus“ und gibst dann folgende Formel ein:
=UND(E2<>"";ISTFEHLER(SVERWEIS(E2;Dropdown;1;falsch)))
Und legst zum Beispiel eine gelbe Hintergrundfarbe fest:
Formatierungsregel definieren
Achte beim Eingeben der Formel unbedingt darauf, dass die angegebene Zelle (bei mir E2) auch tatsächlich mit der momentan aktiven Zelle übereinstimmt.
Die Formel prüft im ersten Teil, ob überhaupt etwas im Ort eingetippt wurde. Ansonsten würde bei jeder neuen Zeile bereits die Ortsspalte gelb eingefärbt werden.
Der zweite Teil der Formel prüft über einen SVERWEIS, ob der Wert in E2 in der Dropdown-Liste enthalten ist. Falls nicht, wird ein #NV-Fehler zurückgeliefert, der über die umschließende ISTFEHLER-Funktion in ein logisches WAHR umgewandelt wird. Erst wenn beide Bedingungen erfüllt sind, greift die Formatierungsregel.
Wenn du jetzt einen neuen Ort eintippst, wird dieser solange gelb hervorgehoben, bis die Pivot-Tabelle einmal aktualisiert wurde. Also bis du Strg+Alt+F5 drückst oder spätestens beim nächsten Öffnen der Datei, wenn die Pivot-Tabelle automatisch aktualisiert wird.
Der fehlende Ort wird hervorgehoben
Es gibt natürlich auch andere Wege, Dropdown-Listen dynamisch zu erweitern und zu sortieren. Insbesondere Microsoft 365 macht hier mit den dynamischen Array-Funktionen das Leben deutlich einfacher. Aber ich finde, der Einsatz einer Pivot-Tabelle an dieser Stelle hat durchaus seinen Reiz. Und vor allem, wenn man kein Microsoft 365 im Einsatz hat.
Was meinst du?
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.
Super, diese Eingabemöglichkeit habe ich immer gesucht. Und nebenbei hervorragend erklärt. Ich werde mich wohl in Zukunft öfter mal mit Pivot-Tabellen beschäftigen.
Vielen Dank und Beste Grüße,
Bo Rado
Hallo Bo,
ja, Pivot-Tabellen sind ein lohnendes Betätigungsfeld. Und sie sind wirklich nicht so schwer, wie viele meinen.
Schöne Grüße,
Martin
Ich habe das Buch „Pivot-Tabellen für Dummies“ von Martin gekauft. Es ist sehr gut strukturiert und lesbar; ich kann es sehr empfehlen.
Hallo Michael,
vielen Dank für den Kauf und für deine Weiterempfehlung!
Schöne Grüße,
Martin
Hallo Martin,
danke, diese dynamisch erweiterte Dropdownliste kam für mich zur richtigen Zeit, genau danach hatte ich gesucht, kann ich wunderbar in meine Projektdaten einarbeiten. Ganz prima 🙂
Liebe Grüße Roswitha
Hallo Roswitha,
na dann passt es doch, freut mich zu hören!
Schöne Grüße,
Martin
Da kann ich den vorherigen Kommentaren nur zustimmen. Auch ich habe schon lange nach einer solchen Möglichkeit gesucht. Klar hätte ich einen Makro schreiben können, aber so finde ich es viel besser. Nur für die die automatische Aktualisierung brauche ich nun noch einen sehr einfachen Makro.
Viele Grüße
Andreas
Hallo Andreas,
ja, mit einem kleinen Makro lässt sich auch der letzte Schritt automatisieren.
Schöne Grüße,
Martin
So jetzt habe ich mal meine erste vollautomatische dynamische Dropdown-Liste erstellt.
Für alle, die sie Aktualisierung auch per Makro vornehmen wollen hier das passende Statement, was beim SheetChange-Event aufgerufen werden müsste:
Tabelle_xyz.PivotTables(„<<>>“).PivotCache.Refresh
Übrigens: Falls die Quellspalte leere Zellen enthält (das ist bei mir der Fall und soll so sein), dann erscheint am Ende der Pivot-Tabelle der Eintrag (leer). Dieser soll natürlich nicht mit in der Dropdown-Liste erscheinen. Das ist leicht bewerkstelligt: einfach am Ende des Bereich.Verschieben-Statements statt -1 nun -2 eintragen. Schon passt alles.
Das funktioniert alles super.
Martin, Dir noch mal vielen Dank für die Idee! Das war sicher nicht das letzte Mal, dass ich diese Idee eingesetzt habe.
Gruß Andreas
Freut mich sehr und Danke für die Ergänzung!
Schöne Grüße,
Martin
Sehr toller Ansatz. Ich bin noch am Lernen und habe nicht verstanden, wie bzw wo ich den „Name bearbeiten“-Dialog bekomme, wo ich auch den Bezug eingeben kann.
Hallo Artur,
die Namen lassen sich im Namensmanager bearbeiten, die Schaltfläche dazu findest du im Menü „Formeln | Namens-Manager“
Schöne Grüße,
Martin
Diese Idee gefällt mir außerordentlich gut und einfach zu handhaben. Super.
Danke, freut mich sehr!
Schöne Grüße,
Martin
Also ohne Pivot habe ich das schon lange auf dem Schirm. Aber die Sortierung und der Änderungsdienst ist SUPER
Sorry, die Tippfehler bitte belächeln
Hallo Gerhard,
vielen Dank für das nette Feedback (Tippfehler? Welche Tippfehler? 🙂 )
Schöne Grüße,
Martin
Hallo und vielen Dank für die tolle Lösung.
Ich habe eine Frage zur bedingten Formatierung:
die zweite Bedingung in der Formel verstehe ich nicht ganz… Es wird überprüft, dass E2 nicht leer ist UND? Was passiert im zweiten Teil, wofür steht der Sverweis?
Danke
Hallo Konstantin,
die Funktion des SVERWEIS ist oben im Artikel eigentlich schon beschrieben 😉
Schöne Grüße,
Martin
Stimmt, habe es übersehen 🙂
Hallo Martin, ich muss das jetzt mal loswerden. Ich verfolge Deinen Block schon seit geraumer Zeit und ich bin immer wieder begeistert was und wie Du das klar verständlich und sehr guten Beispielen darstellst. Bitte mach weiter so. Ein Fan von Tabellen-Experte
Hallo Gerald,
vielen Dank für dieses schöne Feedback. Es freut mich immer wieder zu hören, wenn die Artikel gut bei meinen Lesern ankommen!
Schöne Grüße,
Martin
Guten Abend Martin,
die Idee die Dropdownliste auf Basis von BEREICH.VERSCHIEBEN und einer Pivot-Tabelle zu verwenden ist eine sehr elegante Idee mit den kleinen Schönhgeitsfehler, dass sich die Pivot-Tabelle nicht automatisch aktualisiert.
Dieses ist der Grund, warum ich bei einer privaten Anforderung dann unter Office 365 die Formel SORTIEREN oder SORTIERENNACH verwendet habe und das Ergebnis dieser Matrixformel ebenfalls per Namenszuweisung verwendet habe. Alternativ hätte sich noch ein Bezug auf die Spalte der intelligenten Tabelle angeboten, die aber leider den Nachteil hat, dass hier die Daen nicht sortiert alphabetisch sortiert werden.
Anwendungsfall war bei mir eine Materialliste die zu einzlenen Produkten zusammengestellt werden sollte.
Ich glaube aber, dass deine Variante insbesondere für vor Office 365 Versionen eine sehr gute Idee darstellt und würde auf diese dann tatsächlich gerne verweisen, wenn ich das Beispiel bei mir im Blog aufgreife :-))). Es scheint so als würden gerade mehrere Blogs sich mit der sortierten Auswahl von Dropdownlisten beschäftigen… ??
Viele Grüße
Andreas
Hallo Andreas,
vielen Dank für deine Ergänzungen. Du hast völlig Recht: mit der aktuellen Office 365-Version und den neuen Array-Funktionen gibt es ungleich einfachere Varianten für diese Aufgabe. Und man muss sich auch nicht mit der manuellen Aktualisierung einer Pivot-Tabelle herumschlagen. Daher würde ich es genauso machen, wenn es die Umgebung zulässt.
Aber manchmal kommt man halt nicht drum herum…
Schöne Grüße,
Martin
Wow, du bist mein persönlicher Held! Ich habe ewig mit einer manuellen Sortierung verbracht, die überhaupt nicht funktioniert hat. Frustriert und genervt habe ich dann mit einer automatischen Lösung experimentiert. Zum Glück habe ich deinen Beitrag gefunden. Tausend mal Danke!
Hi Michael,
freut mich sehr zu hören, dass der Artikel weitergeholfen hat.
Schöne Grüße,
Martin
Hallo, ich bastel grade einen Ernährungsplan zum kostenlosen Download. Anfangs war ich recht ambitioniert, inzwischen etwas weniger. Auf jeden Fall hast du mir gerade sehr geholfen. Wenn ich mit dem Basteln fertig bin, darf ich mich vielleicht auch als Excel Experte bezeichnen ?
Sehr gute Lösung, ich habe sie komplett in meinem Ernährungstagebuch umgesetzt und jetzt aktualisiert sich auch
endlich die Dropdown Auswahl. Leider Nutze ich immer zwei Blätter, eins mit den Lebensmitteln und in der nächsten Spalte
die Kalorien je 100g/ml und danach kommt die Pivot-Tabelle. Auf einem anderen Blatt ist dann der laufende Monat und
in der zweiten Spalte das Dropdown Auswahlfeld. Ich suche noch nach der Lösung wie ich dort einen neues Lebensmittel
eingebe und dieses dann auf dem anderen Blatt an die Spalte der Lebensmittel angehängt wird.
Mal sehen was ich da noch finde.
Ansonsten eine sehr interessante Seite mit vielen gute Ausarbeitungen.
Hallo Franky,
danke für das nette Feedback. Ich kenne jetzt die Details in deinen Tabellen nicht, aber um die Daten aus zwei Tabellen zusammenzuführen, käme eventuell Power Query in Frage.
Schöne Grüße,
Martin
Durch Google habe ich deinen Blog und damit die Lösung für mein Problem gefunden.
Getreu dem Motto „Das Problem sitzt meistens vor dem Computer.“
Mit dem manuellen Aktualisieren der Tabelle kann ich umgehen, auch wenn eine Lösung ohne schöner wäre.
Liebe Größe, Michael
Hallo Michael,
freut mich, dass du eine Lösung für den Problem gefunden hast. Ohne Programmierung kann man halt manchmal auf manuelle Schritte nicht ganz verzichten. Aber es hält sich in diesem Fall ja in Grenzen.
Schöne Grüße,
Martin
Lieber Martin
Vielen Dank für deinen Blog. Ich finde immer wieder Antworten für meine Excelfragen. Diesmal leider nicht oder habe ich zu wenig genau gesucht?
Ich habe eine Dropdownliste mit vielen Einträgen (hunderte). Darin zu suchen dauert länger als die Namen zu tippen. Ist es möglich, dass mir Excel meine Eingabe in die Zelle mit den Werten aus der Liste ergänzt, so dass ich nur solange tippen muss bis der gewünschte Eintrag erscheint und ich direkt Enter drücken oder mit der Maus klicken kann, um den Wert in die Zelle zu übernehmen?
Vielen Dank für deine Unterstützung.
Gruss
Andres
Hallo Andres,
mit einem normalen Dropdownfeld lässt sich das leider nicht realisieren. Dafür musst du auf das Kombinationsfeld aus den ActiveX-Steuerelementen (in den Entwicklertools) zurückgreifen.
Schöne Grüße,
Martin
Hallo Martin,
Vielen Dank für den Tipp. Ich werde mir das anschauen.
Schöne Grüsse
Andres
Wenn dein DropDown nur in einem Feld ist geht das: Google Stichwort Dursuchbare Dropdowns (leider auf dieser sehr wertvollen Website nicht gefunden).
Ich suche das ganze aber so, dass man das auch machen kann wenn ich nicht nur ein Feld mit einer DropDown-Liste habe sondern eine ganze Spalte (formatierte Tabelle). Bin noch nicht fündig geworden…
Liebe Grüße
Hallo Martin,
ich habe die Dropdown erfolgreich eingesetzt, sogar 3 mal in einem Reiter.
Jedoch habe ich nun ein Problem.
Ich habe sehr viele Bezeichnungen, welche ich suchen will, die findet meine Liste jedoch nicht, auch, wenn ich das richtige Wort eintippe.
Bei 2 Dropdownlisten geht es problemlos, bei der dritten nicht. Alle Parameter sind jedoch die gleichen, das habe ich überprüft..
Was mache ich hier falsch?
Hallo Matthias,
da habe ich jetzt auch keine Idee, denn eine Mengenbeschränkung ist mir hier nicht bekannt. Wieviele verschiedenen Bezeichnungen hast du denn in der Liste?
Schöne Grüße,
Martin
Dank der formatierten Tabellen innerhalb von Excel, sind viele Dinge deutlich einfacher und sicherer zu gestallten. Ein Beispiel wäre die Dynamische Nummerierung der genutzten Tabellenzeilen. Hierdurch benötigt man lediglich eine separate Spalte für die Formel „=ZEILE()-ZEILE(DynamischeTabelle1[#Kopfzeilen])“
Was mich wundert und auch ziemlich aufregt, ist jedoch die Tatsache das man durch das formatieren einer Formatierten (Intelligenten/Dynamischen) Tabelle, mit jeder Spalte einen eigenen ansprechbaren dynamischen Datenbereich hat, den man als Liste Auswählen können sollte. Schließlich kann man von jedem Arbeitsblatt innerhalb der Arbeitsmappe direkt auf den Inhalt zugreifen in dem man den Namen der Tabelle und der Spalte angibt. Warum muss also der Umweg über Bereich.Verschieben genommen werden um den sowieso schon Dynamisch vorhandenen Bereich, als Dynamischen Bereich darzustellen? Was ich damit Aussagen will, ist die Tatsache das ich in einer Formel „DynamischeTabelle1[Spalte1]“ eingeben kann und Excel automatisch den selben Datenbereich der Spalte1 innerhalb DynamischeTabelle1 verwendet, der auch durch die Formel „=BEREICH.VERSCHIEBEN(DynamischeTabelle1;1;0;ANZAHL2(DynamischeTabelle1[Spalte1])-1)“ verwendet werden würde.
Wissen Sie zufällig warum Excel sich hier so sperrt?
Ich muss die von mir angegebene Formel korrigieren: „=BEREICH.VERSCHIEBEN(DynamischeTabelle1[Spalte1];0;0;Anzahl2(DynamischeTabelle1[Spalte1]))“
Hallo Dennis,
die Schwierigkeit im beschriebenen Beispiel ist nicht der direkte Zugriff auf die Spalten der formatierten Tabelle. BEREICH.VERSCHIEBEN kommt hier zum Einsatz, weil wir den Umweg über die Pivot-Tabelle nehmen. Und die wiederum war nötig, um jeden Wert nur einmal zu erhalten. Eine Pivot-Tabelle lässt sich leider nicht so gezielt ansprechen, wie das bei einer formatierten Tabelle der Fall ist, daher die umständliche Herangehensweise.
Allerdings gebe ich Ihnen recht, dass auch formatierte Tabellen im Zusammenhang mit Dropdown-Listen noch unnötig zickig sind. Denn als Quellbereich lässt sich auch hier nicht direkt der Spaltenname verwenden, sondern diesem muss zusätzlich über den Namensmanager ein Name zugewiesen werden, den man dann als Quellbereich angeben kann. Und das ist wirklich unnötig und umständlich.
Schöne Grüße,
Martin
Hallo Martin,
Auch für dieses Beispiel habe ich es mit den dynamischen Arrays probiert. Es funktioniert mit einem interessanten Bug. Ich gab zur Probe aaa und die Fehlermeldung löste nicht aus. Nach hin und her probieren ist meine bisherige Erkenntnis, dass wenn der neue Ort der erste in der Sortierung des dynamischen Arrays ist, dann löst die Datenüberprüfung keinen Hinweis aus. Wie sich das bei den Dubletten auswirkt, das habe ich noch nicht überprüft.
Mit freundlichen Grüßen
Martin
Nachtrag:
Ich habe mir irgendwie die Datenüberprüfung durch ständiges Löschen der neuen Zeilen und Ändern der Datenüberprüfung gestört.
Die Datenüberprüfung wurde manchmal gar nicht auf die neue Zeile der Datenbanktabelle übertragen, manchmal wurde sie übertragen, aber es wurde der Hinweis nicht ausgelöst.
Lösung des Bugs: Ich habe leere Zellen kopiert und die Gültigkeit auf die Spalte übertragen. Jetzt funktioniert alles tadellos mit den dynamischen Arrays.
Hallo Martin,
ich habe jetzt selbst nochmal ein wenig mit dynamischen Arrays und Dropdowns herumexperimentiert und bin im Moment nicht zufrieden damit. Sobald ich in der formatierten Tabelle einen neuen, bisher noch nicht vorhandenen Ort eingebe, wird dieser trotzdem von der Datenprüfung akzeptiert. Die Quelle basiert auf folgender Formel:
=SORTIEREN(EINDEUTIG(tblAdressen2[Ort]))
Als Verweis habe ich in der Datenprüfung die erste Zelle angegeben:
=$J$2#
Meines Erachtens liegt das Problem darin, dass beim Bestätigen der Eingabe auch das dynamische Array automatisch erweitert wird und somit auch die Quelle für Datenprüfung. Daher schlägt sie nicht an. Ich muss das noch ein wenig intensiver testen, aber im Moment sieht das nach einem Henne-Ei-Problem aus.
Schöne Grüße,
Martin
Hallo Martin,
Danke für dein Nachhaken. Bei mir war bei der Datenüberprüfung der Wurm drin. Da dachte ich schon es würde funktionieren. Nach mehrstündigen Probieren irgendwie einen kleinen Delay in der Aktualisierung der dynamischen Arrays zu erzeugen hat es geklappt, indem ich die Berechnungsmethode auf manuell gestellt habe. Dann werden die dynamischen Arrays erst durch den Nutzerbefehl aktualisiert.
Mit freundlichen Grüßen
Martin
ist es auch möglich eine tabelle mit dropdown zu erstellen wo ich dann 4 pivot tabellen selektiere und z.b. Top10 mir anschauen.
top10 verkäufe, top10 service, top10 werstatt usw.
also eine tabelle und 4 einträge dropdown
Hallo Steffi,
alle Einträge, die du in einem Dropdown-Feld anbieten möchtest, müssen irgendwo in Form eine Liste bereitgestellt werden. Das heißt, die brauchst also beispielsweise die Text „Top10 Verkäufe“, „Top10 Service“ in der Datenprüfung. Und dann wäre es natürlich denkbar, über Verweis-Funktionen auf die entsprechende Pivot-Tabellen zuzugreifen. Diese müssten aber die Ergebnisse schon anzeigen. Du kannst also beispielsweise nicht mit dem Dropdown-Feld die Pivot-Tabellen erst noch irgendwie filtern.
Schöne Grüße,
Martin
Hallo, Martin, wieder mal ein sehr pfiffiger und praxisnaher Tipp, vielen Dank!
Hallo Michael,
freut mich, wenn der Artikel gefallen hat!
Schöne Grüße,
Martin
Hallo Martin,
vielen Dank für den interessanten Artikel. Den hätte ich letzte Woche brauchen können – für einen anderen Anwendungsfall: ich habe per Power Query Daten ins Datenmodell geladen und lasse dann mithilfe des Datenschnitts eine Vorauswahl zu (in deinem Beispiel wäre das der Ort). Für die (in deinem Fall) Straße sollte ich dem Anwender einen Zellendropdown anbieten. Das funktioniert mit dem von dir beschriebenen Vorhehen ideal, da sich die Pivot Liste automatisch nach der Vorauswahl aktualisiert…
Trotzdem vielen Dank für die (wie immer) sehr ausfuhren Beschreibung und beim nächsten Mal bin ich schlauer…
Matthias
Hallo Matthias,
gern geschehen und vielen Dank für dein tolles Feedback.
Schöne Grüße,
Martin
Ich hatte mal einen Fall (gut 20 Jahre her) wo ich viele Adressen (und weiteres) eintippen sollte. Dazu gehörte entsprechend der Ort, die PLZ und das „zuständige Finanzamt“. Da ich in der „Provinz“ gearbeitet habe, hatte ich nicht wirklich mit „Orten mit mehreren PLZ“ zu tun – deshalb konnte ich das gut nutzen (oder man müsste die passende PLZ dann entsprechend manuell doch hinzufügen).
Hab mir zunutze gemacht, dass man ja in Excel die Ausfüll-Automatismen nutzen kann und den „Ort“ an erste Stelle gesetzt. Weiterhin habe ich das Chance-Ereignis mit VBA programmiert.
Darin habe ich in einem zweiten Tabellenblatt eine „Datenbank“ erstellt, die sich peu a peu füllte bzw. Daten auslas – eben für Ort, PLZ und „zuständiges Finanzamt“.
Wenn ich einen Ort eintippte, sah ich ja schnell ob es diesen schon gab und hatte eine kurze Eingabe vor mir (außer es gab ihn nicht). Wenn ich mit „Tab“ das Feld verließ hat er das Makro angeschmissen und geprüft, ob es den Ort in meiner Liste schon gab. Wenn nein, hat er nichts gemacht und den Cursor in die nächste Spalte verschoben; gab es den Ort bereits, hat er mir die zwei folgenden Spalten automatisch ausgefüllt und den Cursor auf die neue Zeile positioniert. Wenn ich danach PLZ und „zuständiges Amt“ eingetippt habe, hat er wieder das Makro gestartet und die drei Daten in mein zweites Tabellenblatt geschrieben.
So hatte ich eine automatisch wachsende Datenbank und konnte mir viele Eingaben sparen – ganz ohne Klappliste. 🙂
So eine Idee kann man garantiert auch vielseitig nutzen (oder gleich auf eine UserForm zugreifen die eine ComboBox enthält/füllt).
Hallo Tobias,
klar, mit VBA gibt es natürlich sehr komfortable Möglichkeiten, dieses Problem zu lösen. Hört sich auf jeden Fall nach einer kreativen und schönen Lösung an. Man muss nur erwähnen, dass mittlerweile in vielen Firmen der Einsatz von VBA aus Sicherheitsaspekten immer mehr eingeschränkt oder sogar ganz verhindert wird. Aber dort, wo es möglich ist, wäre das auf jeden Fall eine Alternative.
Schöne Grüße,
Martin
Hallo Martin,
vielen Dank für die tolle Erklärung.
Vielleicht kannst/magst du mir bei einem anderem Problem mit einer Antwort aushelfen.
Ich habe ein Dropdown Menü erstellt in diesem befindet sich zur Auswahl Mitarbeiter 1 / Mitarbeiter 2 usw
wenn ich Mitarbeiter 1 auswähle sollen diese Stunden der Schicht (Zeiten sind ebenfalls mit dropdown auswählbar) in meiner Gesamttabelle ausgerechnet werden für die Std. Tgl/Woche/Monat
Wie kann ich es Festlegen das Mitarbeiter x die Stunden automatisch zugewiesen werden wenn ich Ihn für eine bestimmte Schicht über das Dropdown Feld ausgewählt/bzw gesetzt habe ?
Danke für Hilfe.
Viele grüße
Mijago
Hallo Mijago,
danke für dein schönes Feedback.
Ich kenne jetzt den Aufbau deiner Tabellen und die genauen Logiken nicht. Aber wenn es für jeden Mitarbeiter eine fixe Stundenanzahl gibt, dann könntest du diese in einer kleinen Referenztabelle hinterlegen (Mitarbeitername, Stunden). Und dann über einen SVERWEIS oder XVERWEIS auf Basis des Mitarbeiters die passenden Stunden aus der Tabelle holen.
Das heißt, das zweite Dropdown-Feld wäre nicht notwendig.
Schöne Grüße,
Martin