Dropdown-Liste: Dynamisch erweitert und sortiert 46

Eine etwas andere Art, wie man Auswahllisten dynamisch gestalten kann.
 

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:

Das Szenario

Nehmen wir an, du hast eine Adressenliste (meine Beispieldatei kannst du hier herunterladen), 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“:

Umwandeln in eine formatierte Tabelle

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:

PivotTable-Tools | Entwurf |Gesamtergebnisse | Für Zeilen und Spalten deaktiviert

Ergebniszeile deaktivieren

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. Aber ich finde, der Einsatz einer Pivot-Tabelle an dieser Stelle hat durchaus seinen Reiz. Was meinst du?

 

Wenn dir der Artikel gefallen hat: Bitte weitersagen!
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.



Avatar-Foto

Über Martin Weiß

Er ist das Gesicht hinter dem Blog "Der Tabellenexperte". Seit 2013 veröffentlicht er hier Beiträge zu seinem Lieblingsprogramm: Microsoft Excel. Martin Weiß ist zertifizierter Microsoft Excel Expert und verdient sein Geld als selbständiger Excel-Berater, -Entwickler und -Trainer.

Schreibe einen Kommentar zu Konstantin Antworten abbrechen

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

46 Gedanken zu “Dropdown-Liste: Dynamisch erweitert und sortiert

  • Avatar-Foto
    Bo Rado

    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

    • Avatar-Foto
      Martin Weiß

      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

    • Avatar-Foto
      Michael Krüger

      Ich habe das Buch „Pivot-Tabellen für Dummies“ von Martin gekauft. Es ist sehr gut strukturiert und lesbar; ich kann es sehr empfehlen.

  • Avatar-Foto
    Roswitha

    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

  • Avatar-Foto
    Andreas

    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

    • Avatar-Foto
      Martin Weiß

      Hallo Andreas,

      ja, mit einem kleinen Makro lässt sich auch der letzte Schritt automatisieren.

      Schöne Grüße,
      Martin

      • Avatar-Foto
        Andreas

        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

        • Avatar-Foto
          Artur

          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.

          • Avatar-Foto
            Martin Weiß

            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

  • Avatar-Foto
    Gerhard Duscha

    Also ohne Pivot habe ich das schon lange auf dem Schirm. Aber die Sortierung und der Änderungsdienst ist SUPER

  • Avatar-Foto
    Konstantin

    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

  • Avatar-Foto
    Gerald

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Andreas Unkelbach

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Michael

    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!

    • Avatar-Foto
      Michael

      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 ?

  • Avatar-Foto
    Franky jr.

    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.

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Michael

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Andres Müller Krummenacher

    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

    • Avatar-Foto
      Martin Weiß

      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

      • Avatar-Foto
        Andres Müller Krummenacher

        Hallo Martin,

        Vielen Dank für den Tipp. Ich werde mir das anschauen.

        Schöne Grüsse
        Andres

    • Avatar-Foto
      Lea

      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

  • Avatar-Foto
    Matthias

    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?

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Dennis Weisenburger

    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?

    • Avatar-Foto
      Dennis Weisenburger

      Ich muss die von mir angegebene Formel korrigieren: „=BEREICH.VERSCHIEBEN(DynamischeTabelle1[Spalte1];0;0;Anzahl2(DynamischeTabelle1[Spalte1]))“

      • Avatar-Foto
        Martin Weiß

        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

  • Avatar-Foto
    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

    • Avatar-Foto
      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.

      • Avatar-Foto
        Martin Weiß

        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

        • Avatar-Foto
          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

  • Avatar-Foto
    Steffi

    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

    • Avatar-Foto
      Martin Weiß

      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