Auswahllisten oder Dropdown-Listen sind in Excel extrem beliebt. Sie erleichtern dem Anwender die Dateneingabe, verhindern Fehler und lassen deine Tabelle einfach professionell erscheinen.
Es gibt nur ein kleines Ärgernis: Wenn neue Einträge in der Auswahlliste dazukommen sollen, ist meistens Handarbeit angesagt.
Heute zeige ich dir, wie man eine Dropdown-Liste erstellt, die
- automatisch neue Einträge aufnimmt
- alphabetisch sortiert ist
- keine doppelten Einträge enthält
Und das alles (fast) vollautomatisch!
Und so geht’s:
Beginnen wir beim Ende
Ich habe eine kleine Beispieltabelle vorbereitet, die du bei Bedarf hier herunterladen kannst. Sie enthält eine lange Länderliste und ihre Zuordnung zu den Kontinenten:
Das Endergebnis
Nun möchte ich in Zelle E1 eine Dropdown-Liste haben, die mir alle Kontinente aus der Liste anbietet. Und im Feld E2 soll dann die Anzahl der Länder im ausgewählten Kontinent berechnet werden.
Wie bekomme ich nun meine Kontinent-Dropdown-Liste so hin, dass
a) jeder Kontinent nur einmal vorkommt
b) neue Kontinente, die am Ende meiner Länderliste ergänzt werden, automatisch übernommen werden
c) die Liste immer alphabetisch sortiert ist
Die Lösung führt über eine…
…Pivot-Tabelle
Vorbereitungsarbeiten
Auf den ersten Blick nicht ganz naheliegend, bei näherer Betrachtung aber genial. Aber alles der Reihe nach.
Basis unserer Pivot-Tabelle ist die Länderliste. Diese formatiere ich zunächst als „intelligente“ Tabelle:
Die „nackte“ Ausgangstabelle
Da sich die aktive Zelle innerhalb meiner Länderliste befindet, schlägt mir Excel bereits den richtigen Tabellenbereich vor. Wichtig ist nur der Haken „Tabelle hat Überschriften“:
Als Tabelle formatieren
Solange sich die aktive Zelle innerhalb der formatierten Tabelle befindet, wird im Menüband ein neuer Eintrag „Tabellentools“ mit der Registerkarte „Entwurf“ angezeigt. Hier überschreibe ich den vorgeschlagenen Tabellennamen mit einem etwas aussagefähigeren, wie z.B. „Länderliste“:
Einen sprechenden Namen vergeben
Einer der großen Vorteile von formatierten Tabellen: Der Bereich, den die Tabelle umfasst, erweitert sich automatisch, sobald man unten neue Einträge anfügt. Somit kann ich über den Namen „Länderliste“ im den kompletten Bereich ansprechen.
Die Pivot-Tabelle
Jetzt erstellen wir aus unserer Länderliste eine extrem simple Pivot-Tabelle. Die aktive Zelle sollte sich wieder irgendwo innerhalb der Liste befinden, dann rufen wir im Menüband im Register „Einfügen“ die Schaltfläche „PivotTable“ auf:
Die Pivot-Tabelle einfügen
Als Tabelle/Bereich zeigt mir Excel bereits den Namen „Länderliste“ an. Als Ziel für meine Pivot-Tabelle gebe ich eine freie Zelle etwas rechts von meiner Tabelle an, z.B. G2:
Pivot-Tabelle im gleichen Blatt
Nun wähle ich aus der Liste der vorhandene Felder „Kontinent“ aus. Die standardmäßige Summenzeile entferne ich über einen Rechtsklick in die Pivot-Tabelle. Hier kann man unter „Pivot-Table-Optionen…“ in der Registerkarte „Summen & Filter“ die beiden Gesamtsummen-Haken entfernen:
Pivot-Tabelle: Die Feldauswahl
Jetzt müssen wir nur noch sicherstellen, dass die Einträge auch immer alphabetisch sortiert sind. Dazu klicken wir einfach auf den Pfeil neben „Zeilenbeschriftungen“ und wählen die Sortieroption „Von A bis Z sortieren“:
Alphabetische Sortierung einstellen
Wie du siehst, nähern wir uns dem Ziel schon: Die Pivot-Tabelle liefert mir eine alphabetisch sortierte, dublettenfreie Liste aller Kontinente.
Damit wir diese aber für unsere Dropdown-Liste verwenden können, müssen wir noch einen Namen definieren.
Dynamische Namen
Die Vergabe eines Namens für eine Zelle oder einen Zellbereich erfolgt über die Registerkarte „Formeln“. Hier gibt es die Schaltfläche „Namen definieren“:
Einen Namen definieren
Als Name vergebe ich z.B. „Dropdown“. Fehlt noch der Eintrag in „Bezieht sich auf“. Da wir nicht wissen können, wie viele Einträge unsere Pivot-Tabelle in Zukunft haben wird, dürfen wir hier keinen festen Zellenbezug eingeben. Stattdessen berechnen wir die Größe des Zellbereichs dynamisch mit der BEREICH.VERSCHIEBEN-Funktion.
Mehr Dynamik mit einer Formel
Die allgemeine Syntax lautet:
=BEREICH.VERSCHIEBEN(Bezug;Zeilen;Spalten;[Höhe];[Breite])
Damit wird also ein gegebener Bezug, also eine Zelladresse um eine bestimmte Anzahl von Zeilen und/oder Spalten „verschoben“, so dass sich ein neuer Bezug ergibt. Dieser kann dann optional auch eine andere Höhe und Breite haben, als der Ausgangsbezug.
In unserem Fall lautet die Funktion also:
=BEREICH.VERSCHIEBEN(Tabelle1!$G$3;0;0;ANZAHL2(Tabelle1!$G:$G)-1)
Die Ausgangszelle G3 (wichtig: mit den Dollarzeichen absolut referenzieren!) ist der erste Eintrag unserer Pivot-Tabelle und das soll auch so bleiben. Daher geben wir für die Parameter Zeilen und Spalten jeweils den Wert 0 (Null) an.
Aber wir wollen eine dynamische Länge der Liste ermitteln (= Parameter „Höhe“). Dies erfolgt über die Funktion ANZAHL2, mit der wir alle Einträge in Spalte G zählen und davon den Wert 1 (für die Kopfzeile) abziehen.
Das funktioniert allerdings nur, wenn in der Spalte G nicht noch irgendwelche anderen Daten vorhanden sind. Gegebenenfalls musst Du die Pivot-Tabelle dann in einer anderen leeren Spalte platzieren.
Die Dropdown-Liste
Jetzt müssen wir nur noch für das Feld E2 die Dropdown-Liste aktivieren. Dazu stellen wir die aktive Zelle in das Feld E1 und klicken in der Registerkarte „Daten“ auf die Schaltfläche „Datenüberprüfung“.
Im Feld „Zulassen“ wählen wir „Liste“ und geben dann im Feld „Quelle“ den Namen ein, den wir zuvor definiert haben (Gleichheitszeichen davor nicht vergessen!):
Datenüberprüfung einstellen
Wie man sieht, liefert die Dropdown-Liste die aktuellen Werte aus der Pivot-Tabelle:
Die Dropdown-Liste im Einsatz
Was passiert nun, wenn wir neue Einträge in unserer Länderliste vornehmen? Ich hänge als einfach mal zwei neue Länder unten an, die natürlich auf einem neuen Kontinent liegen:
Neue Werte in der Länderliste
Damit dieser Eintrag nun in der Dropdown-Liste erscheint, muss nur die Pivot-Tabelle aktualisiert werden. Also die aktive Zelle irgendwo in die Pivot-Tabelle stellen und in den PivotTable-Tools die Schaltfläche „Aktualisieren“ klicken:
Pivot-Tabelle aktualisieren
Und schon erscheint auch in der Dropdown-Liste der neue Eintrag alphabetisch korrekt einsortiert:
Der neue Kontinent erscheint automatisch
Fehlt nur noch in Zelle E2 die Formel, welche die Anzahl der Länder zum gewählten Kontinent liefert. Das erledigen wir mit der ZÄHLENWENN-Funktion:
Strukturierte Verweise in ZÄHLENWENN
Hier erkennt man auch einen weiteren Vorteil von formatierten Tabellen: Es werden in den Funktionen, die sich darauf beziehen, automatisch sogenannte strukturierte Verweise verwendet. Statt z.B. B2:B200 steht dann ein wesentlich aussagefähigeres „Länderliste[Kontinent]“.
Bonus-Tipp zum Schluss:
Man kann auch einstellen, dass die Pivot-Tabelle (und damit die Dropdown-Liste) zumindest bei jedem neuen Öffnen der Datei aktualisiert wird. Dazu öffnet man die PivotTable-Optionen:
Pivot-Table-Optionen aufrufen
Jetzt noch im Register „Daten“ das Häkchen „Aktualisieren beim Öffnen der Datei“ setzen:
Automatisch aktualisieren beim Öffnen der Datei
Fertig!
Wie du siehst, gibt es auch für Pivot-Tabellen durchaus exotische Einsatzmöglichkeiten.
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.
Drop-Down-Liste Spezial:
Wahrlich ein meisterlicher Beitrag. Danke für die Tipps.
Grüß dich Martin,
Danke für den Tipp mit der Pivottabelle!
Anstelle der etwas unschönenen (da volatil) Funktion Bereich.Verschieben würde ich die Index-Formel in der Bezugsvariante empfehlen.
Im Namensmanager bei „Bezieht sich auf“: =INDEX(Tabelle1!G:G;3):INDEX(Tabelle1!G:G;ANZAHL2(Tabelle1!G:G))
Das Ergbnis ist das gleiche 😉
Viele Grüße
Marcel
Hallo Marcel,
danke für den Tipp mit der INDEX-Funktion. Es führen mal wieder viele Wege nach Rom… 🙂
Schöne Grüße,
Martin
Hallo Marcel,
ich staune, daß bisher noch Niemanden aufgefallen ist, daß Deine Index-Formeln 2 Zellen zu wenig erfassen und damit in der Gültigkeitsliste dann fehlen.
Das erste Index kann man weglassen, da auch dies hier sauber funktioniert:
Tabelle1!$G$3:INDEX(Tabelle1!$G:$G;ANZAHL2(Tabelle1!$G:$G)+ZEILE(Tabelle1!$G$3)-1)
Zudem ist es ratsam, auch ganze Zeilen/Spalten absolut zu setzen, da es schnell mal passieren kann, daß man sich im Namensmanager vertut und dann die Zeilen/Spalten plötzlich wandern.
Gruß von Luschi
aus klein-Paris
Hallo Martin,
als nicht ganz unerfahrener Excel-Anwender haben mich die meisten „Gurus“ mit ihren angeblich tollen Tips meist gelangweilt.
Endlich habe ich die Webseite gefunden, bei der ich noch was lernen kann.
Danke
Frank
Hallo Frank,
so etwas freut mich natürlich zu hören!
Schöne Grüße,
Martin
Hallo, ich stehe vor folgender Herausforderung: eine Tabelle mit zwei Spalten. In Spalte B wähle ich einen Wert aus einer Drop-Down-Liste aus. Je nach ausgewähltem Wert soll die zeilengleiche Zelle in Spalte A mit einer farblichen Füllung versehen werden (quasi eine bedingte Formatierung, die sich an der Auswahl in Zelle B orientiert). Geht sowas ohne Makro?
Die Tips sind übrigens alle goldwert. Vielen Dank. Eine super Seite.
Hallo Jürgen,
danke für das Lob.
Was Dein Problem angeht: Genau dazu sind bedingte Formatierungen da. Man kann Regeln auch in Abhängigkeit von Werten in anderen Zellen definieren. Dabei musst Du als Regeltyp nur den letzten Eintrag „Formel zur Ermittlung der zu formatierenden Zellen verwenden“ auswählen und die gewünschte Bedingung/Formel eingeben.
Alles ohne Makro/VBA.
Schöne Grüße,
Martin
Hallo Martin,
gibt es in Excel auch eine Möglichkeit für eine Dropdown-Liste aus der ich mehrere Dinge auswählen kann?
Beispiel: Ich habe eine Liste mit Personen, die verschiedene Instrumente spielen. Die Instrument möchte ich über eine Dropdown-Auswahl wählen. Das Problem: Einige Personen spielen zwei oder drei verschiedene Instrumente…
Wie kann ich das lösen, ohne weitere Spalten für weitere Instrumente anzulegen? Ich möchte am Ende nämlich auch in der Lage sein zu filtern, z.B. nach allen Personen, die Klarinette spielen und das ist meiner Meinung nach schwierig wenn sich die Instrumente über mehrere Spalten verteilen.
Vielen Dank in Voraus 🙂
Mieke
Hallo Mieke,
nein, die Möglichkeit, mehrere Einträge in einem Dropdown-Feld auszuwählen, gibt es nicht. Wenn Du nach dem beschriebenen Fall filtern möchtest, musst Du wohl oder übel auf andere Krücken zurückgreifen, z.B. pro Person und Instrument einen eigenen Datensatz (Zeile) anlegen.
Schöne Grüße,
Martin
Danke für den ausführlichen Artikel, super erklärt.
VG
Klaas
Hi Martin,
in einer Stunde lockeren des Lesens quer durch die Seite habe ich mehr sinnvolle Infos erhalten, als in 24 Stunden verschärften Wühlens durch die Excel-Hilfe.
Weiter so…
Hi Matthias,
das hört man gerne, vielen Dank!
Schöne Grüße,
Martin
Hallo Martin, ich finde die Dropdown Liste Spezial echt klasse..Danke, habe einiges dazu gelernt ..Habe nur noch eine Frage, wie bekomme ich durch eine Privot Liste mit untertitel(verschiede Tätigkeit) in die dropdown liste. also habe verschiede Tätigkeiten und es muss sortiert in die Dropdown Liste. Denke muss den letzen Schritt (Formel) ändern. Statt nur die Ausgewählte Tabelle, noch den Untertitel eingebn. Nur wie? Wäre dir sehr dankbar für die Hilfe.
Gruss Olga
Hallo Olga,
ich bin mir nicht ganz sicher, ob ich Dich richtig verstanden habe. In meinem Beispiel ist es ja so, dass die Pivot-Tabelle nur die Ländernamen enthält. Je nachdem, wie Deine Tabelle aufgebaut ist, musst Du in der Pivot-Tabelle eben nur das Feld aufnehmen, welches Du für die Dropdown-Liste benötigst, also beispielsweise die Tätigkeiten. Die Formel für den dynamischen Bereich (mit BEREICH.VERSCHIEBEN) bleibt dann grundsätzlich auch gleich.
Oder habe ich Dich falsch verstanden?
Schöne Grüße,
Martin
Danke für schnelle Rückmeldung Martin 🙂 Ja soweit hatte ich es. Ich habe aber 5 Verschiede Tätigkeiten in meiner Tabelle. Wenn ich mir die Privot Tabelle aufbaue , quasi für jede Tätigkeit eine Tabelle , würde es ja gehen.(Dann habe ich aber 5 Tabellen) Aber ich überlege, vielleicht geht es mit einer Tabelle so, dass er mir immer die Tätigkeit raussucht und die Mitarbeiter dann zusammenfügt und Dropdown. Hoffe konnte es richtig beschreiben .
Viele Grüße
Olga
Martin, ich überlege. Vielleicht gibt es da bessere Lösung als Privot Tabelle. Erkläre es noch mal kurz. Habe eine mitarbeiter Liste mit Namen und Tätigkeiten(von den Mitarbeiter). Ich baue einen Dienstplan auf. Die Mitarbeiter muss ich in der Tabelle per Dropdown finden. Z.b. von allen Säger sollen die Namen rauskommen. Soll mir aus der Tabelle alle Säger Namen wiedergeben. Weiß nicht wie ich es hinkriege ohne extra Tabellen usw. Wäre dir wirklich dankbar, wenn ich hier weiterkomme.
Gruß
Olga
Hallo
super Seite mit tollen Tipps
Leider hab ich trotzdem noch eine Herausforderung, wo ich hänge.
Als Datengültikeit gibt es eine Tabelle mit Belegnummern. (Benannt als Belege)
Auf einem anderen Blatt möchte ich über Dropdown / Gültigkeiten die Einträge aus Belege angezeigt bekommen.
Das ist ja kein Problem.
… Liste und bei Quelle =Belege rein
Aber die Werte, die in der Spalte bereits „vergeben“ (weiter oben oder unten) sind, sollen im Dropdown NICHT mehr angezeigt werden.
Wird ein Wert (weiter oben oder unten) gelöscht, ist der Wert wieder wählbar.
Erschwert wird das ganze noch, dass es in der Eingabespalte 10.000 Eingaben geben wird.
Die Vorschlagsliste wird also immer kürzer.
Gibt es dafür eine Lösung?
Ich würde mich über eine Hilfe riesig freuen
Danke und Gruß Uwe
Hallo Uwe,
da gibt es schön Lösungen dafür, die sind aber nicht mit ein paar Sätzen hier erklärt. Ich möchte Dir mein E-Book „Dropdown-Listen in Excel“ nicht aufdrängen, aber dort werden solche Themen auch behandelt. Vielleicht wäre das ja interessant für Dich.
Schöne Grüße,
Martin
Danke für die Info
LG Uwe
Danke für deinen Beitrag. Ich habe jedoch gerade folgendes Problem bzgl. Dropdownmenus.
In einem Dropdownmenü sind verschiedene Bezeichnungen hinterlegt, z.B. Datum, Tag, Uhrzeit etc.
Da die Datei später von Benutzern mit verschiedenen Sprachen genutzt wird, sollen die Benutzer die Bezeichnungen in der Quelltabelle übersetzen. Das Dropdownmenü passt sich dann automatisch an.
Allerdings hätte ich es gerne so, dass wenn z.B. „Datum“ im Dropdownmenü ausgewählt wurde und der Benutzer in der Quelltabelle „Datum“ mit „Date“ überschreibt, nicht nur die Dropdownliste aktualisiert wird, sondern auch direkt der ausgewählte Punkt in der Dropdownliste aktualisiert wird, ohne dass der Benutzer dann nochmal extra „Date“ auswählen muss. Gibt es für dieses Problem eine Lösung?
Hallo Georg,
einmal aus der Dropdown-Liste ausgewählte Punkte können mit normalen Mitteln nicht automatisch geändert werden, dazu wäre VBA erforderlich. Gegebenenfalls kann man den Anwender aber auf die nunmehr ungültigen Einträge per Bedingter Formatierung zumindest hinweisen.
Schöne Grüße,
Martin
Hallo Martin,
vielen lieben Dank für den Super-Tipp – in der Tat sehr hilfreich..und doch so einfach.
Ich habe eine Frage und zwar habe ich mir auf die beschriebene Weise drei eindeutige Dropdown-Listen erstellt, die bisher alle Werte der Tabelle enthalten. Da jedoch nicht alle Kombinationen möglich sind, möchte ich die Auswahl basierend aus dem vorherigen Dropdown beschränken. (z.B. wenn als Versandart „Luftfracht“ ausgewählt wurde, dass nur die Luftfracht-Spediteure angezeigt werden und wenn der Spediteur ausgewählt wurde im nächsten Dropdown nur die Lieferanten, die diesen Spediteur nutzen). Ist eine Verschachtelung über die Indirekt-Funktion möglich?
Viele liebe Grüße!
Anne
Hallo Anne,
zum Thema „verschachtelte/abhängige Dropdown-Listen“ verweise ich gerne auf mein E-Book. Dort werden (unter anderem) mehrstufige Dropdown-Listen detailliert beschrieben:
Dropdown-Listen in Excel – Der große Leitfaden
Schöne Grüße,
Martin
Hallo!
Habe das Tabellenbeispiel noch nicht probiert, nur durchgelesen.
Bin sehr überrascht was alles möglich ist im Excel.
Danke dafür!
Selbst habe ich auch ein kleines Problem wobei ich es um es zu lösen hierher gelangt bin.
Meine Buchhaltungstabelle Beinhaltet pro Monat eine Privot Tabelle mit Buchungstext (Kategorie) und Betrag,
die Monatsweise zusammengefasst werden.
Ich würde gerne ein Dropdownmenü anlegen, worin die Kategorien als Liste auswählbar sind. Hab ich auch geschafft,
allerdings übernimmt der Privottable die Auswahl nicht, es erscheint ein leeres Feld anstelle der Kategorie.
Was muß ich im Privottable ändern um die Auswahl (im Dropdown) anzeigen zu lassen?
Ich sag schon mal danke!
Hallo Nik,
ich bin mir nicht ganz sicher, ob ich dich richtig verstanden habe. Innerhalb einer Pivot-Tabelle können keine Dropdown-Felder über die Datenüberprüfung angelegt werden, da die Pivot-Tabelle ja nur die Daten anzeigt, die in der Quelltabelle gespeichert sind. Eine Dateneingabe in einer Pivot-Tabelle ist nicht möglich, daher auch keine Dropdown-Felder.
Oder geht es nicht um die Pivot-Tabelle, sondern um die Datentabelle?
Schöne Grüße,
Martin
Hallo Martin,
tolle Seite! Meine Freundin ist selbstständige Ergotherapeutin und muss ein Fahrtenbuch führen. Dafür habe ich ihr eine Tabelle angelegt (Zeile: Anfahrt 1, Anfahrt 2 usw. Spalte: Montag Dienstag…) Jetzt sollen in die Zellen die Anfahrtsadressen der Patienten geschrieben werden. Da diese aus einem Adresspool stammen und meine Freundin nicht jede Adresse händisch neu eingeben will habe ich eine Gültigkeitsliste gedacht.
Haken und Frage bei der Sache: Wenn sehr viele Adressen/Einträge in der Gütigkeitsliste sind wäre es schön, wenn man diese filtern könnte. Ist das möglich? Also kann ich „Fr. Ha“ eingeben und bekomme dann als Vorschläge aus der Liste „Fr. Hamacher“, „Fr. Hansen“ usw?
Liebe Grüße
Frank
p.s.: Bin programmier erfahren, also wenn es nur über VBA geht bin ich auch hier über hinweise dankbar 😉
Hallo Frank,
diese Art von Gültigkeitslisten (also mit Eingabehilfe) gibt es nur als ActiveX-Kombinationsfeld. Du musst dazu die Entwicklertools im Menüband aktivieren und dann über die Schaltfläche „Einfügen“ aus der Gruppe der ActiveX-Steuerelemente das Kombinationsfeld auswählen. Aber aufpassen: Die funktionieren nur unter Windows, nicht und Mac!
Schöne Grüße,
Martin
Vielen Dank !
… aber wie mache ich es wenn ich eine Auswahlliste mehrfach benutzen will und diese Liste aber um den vorher gewählten Wert verringert werden soll. (Bsp. man hat Apfel, Birne, Orange. Die Birne wird gegessen. Danach kann man keine Birne mehr wählen!
Hallo,
das geht, ist aber eine etwas kompliziertere Angelegenheit und hier nicht mit zwei Sätzen erklärt. So etwas wäre in meinem Dropdown-Leitfaden beschrieben (https://www.tabellenexperte.de/der-grosse-dropdown-leitfaden/)
Schöne Grüße,
Martin
Vielen Dank für diesen tollen Beitrag!
Ich habe es mal gleich umgesetzt und kann nun viel effektiver arbeiten.
Insbesondere bei der Erstellung von Angeboten an meine bereits vorhandenen Kunden macht das durchaus Sinn.
Beste Grüße an alle 🙂
Gern geschehen.
Schöne Grüße,
Martin
Der Beitrag hat mir sehr geholfen. Vielen Dank dafür!
Guten Tag ich versuche eine Dropdownliste zu erstellen.
In Mappe 1 habe ich eine Tabelle mit Material und in Mappe 2 möchte ich ein Dropdownfeld erstellen wo ich dann das Material auswählen kann.
Habe es mehrfach versucht leider klappt dieses nicht.
Hallo John,
da deine Beschreibung sehr spärlich ist, kann ich nur Vermutungen anstellen. Wenn du noch Excel 2007 im Einsatz hast, dann ist es nicht direkt möglich, in der Datenprüfung eine Quelle in einem anderen Arbeitsblatt anzugeben. Du musst stattdessen der Materialliste einen Namen geben und kannst dann in der Datenprüfung den Namen eintragen. Das funktioniert auch in Excel 2007.
Schöne Grüße,
Martin
Ein Tolle Webseite mit viel Tipps als Experte. Respekt an das Team. ???
Hallo Martin,
ich habe mir Ihre Tabellen Abhandlung gekauft und bin sehr zufrieden.
Ein Thema habe ich jetzt:
Meine Haupttabelle, als Tabelle formatiert, sie nennt sich Störfalliste, enthält Dropdowns. Sowohl direkte, , als auch dynamische z.B. =indirekt(„tab_nnnn[„&$M2&“]“), M für Spalte absolut adressiert, 2 für Zeile relativ adressiert.
Beim Erweitern der Störfalliste, neue Zeile durch simples eingeben in die nächste freie Zeile, werden die direkten Dropdowns mitgezogen, die dynamischen Dropdowns werden aber nicht mitgezogen.
Wie kann ich das erreichen ohne das Notfallkonzept?
Notfallkonzept wäre ein kurzes Macro:
1. markieren 1tes Feld in der neuen Zeile
2. kopieren & einsetzen und Inhalte löschen
der darüberliegenden Tabellenfelder.
Eine Anmerkung zu Deiner Abhandlung:
bei der Nutzung der dynamischen Datenprüfung hat man sonst ungebräuchliche Zeichen: „;&;[ ];# vielleicht sonst noch welche.
Der Zweck und die formale Anwendung dieser Zeichen im Kontext mit der Datenprüfung dürfte jeden systemisch denkenden interessieren.
Ich habe darüber im Netz nach längerem Suchen nichts gefunden. Man tut sich leichter hinsichtlich des Merkens der Formeln und Syntax, wenn man die Bedeutung kennt. Ein kurzes Kapitelchen darüber empfände ich als sehr aufschlußreich. Das auswendig Lernen ist etwas Problem behaftet. Wenn man die Formel längere Zeit nicht angewendet hat verliert man bei der Anwendung mit Trial & Error immer viel Zeit.
Gruss Klaus Herbertz
Hallo Klaus,
erst einmal Danke dafür, dass Sie meinen Leitfaden gekauft haben.
Das von Ihnen beschriebene Verhalten bei der dynamischen Dropdown-Liste wundert mich allerdings. Denn es ist gerade der Vorteil, wenn man hier mit formatierten Tabellen arbeitet, dass sich die angebotenen Werte automatisch erweitern, sobald man neue Zeilen in der Tabelle eingefügt hat. Ich habe das bei mir gerade nochmal nachgestellt und beide Dropdowns arbeiten so, wie man es erwartet.
Daher habe ich im Augenblick leider auch keinen Tipp für Sie. Kennt vielleicht ein anderer Leser dieses merkwürdige Verhalten?
Schöne Grüße,
Martin
Hi Martin 🙂
vielen Dank für die klasse Beschreibung! .
Habe eine kleine Frage: Gibt es einen Weg wie ich anstelle der direkten Eingabe von „Dropdown“ im Fenster Datenüberprüfung als Quelle den Verweis auf eine Zelle machen kann, wo der definierte Name steht? 😛
Vielen Dank im Voraus
LG Thomas
Hi Thomas,
ja, dafür gibt es die INDIREKT-Funktion. Angenommen, in A1 steht der definierte Name, der auf die eigentliche Liste verweist. Dann gibst Du in der Datenüberprüfung ein:
=INDIREKT(A1)
Schöne Grüße,
Martin
Lieber Martin,
vielen Dank für Deine ausführliche Beschreibung. Ich stehe mit Excel auf „Kriegsfuß“ und arbeite fast immer, wenn es geht altmodisch mit Papier und Bleistift, was natürlich sehr aufwendig ist. Am meisten freue ich mich, wenn mir jemand eine fertig ausgearbeitete Datei schickt. Jedoch habe ich jetzt genau die Aufgabe, so eine Liste zu erstellen. Dank Deiner Anleitung wird es klappen.
Hallo Ute,
dann drücke ich mal die Daumen 🙂
Schöne Grüße,
Martin
Hallo Martin,
Excel beißt nicht, stellt mich immer wieder vor Herausforderungen…. Aktuell habe ich keine Erklärung für folgende Frage:
Erstellt ist eine Pivot mit zahlreichen Kunden mit jeweils 1+x Produktgruppen und den zur Verfügung gestellten Bestelldaten. Die Bestelldaten sind jeweils eigenständigen Merkmalen zugeordnet, die als Spalte in der Quelldatei vorhanden sind. Je Kunden sind diese Merkmalspalten individuell befüllt. Und zwar in den Varianten: nummerisch / alphanummerisch
Die auf Basis der Quelldatei erzeuge Pivot ordnet den Kunden horizontal die übermittelten Bestelldaten nach Merkmalen zu.
Bsp. Kunde KD-Nr. Prüfplan MHD Beistellteile
Die Spalte Prüfplan umfasst alle Werte der Quelldatei (ersichtlich in den Pivot-Table Feldern) ohne Filter. Die Ausgabe der Daten in der Pivottabelle erfolgt aber nur auszugsweise. Nach einem Muster, das nicht erkennbar ist. Und das, obwohl alle Werte der Quelldatei im Pivot Table Feld vorhanden sind. Die Formatierung in der Quelldatei ist einheitlich Standard, das alpha-nummerisch.
Ich bin sicher, dass das Problem vor dem Computer sitzt. Aber schon richtig heftig ratlos ….
Hast Du vielleicht einen ganz schnellen, vor allem aber wirkungsvollen Tipp für mich?
Lieben Dank dafür bereits im Voraus.
Viele Grüße
Isolde
Hallo Isolde,
das ist aus der Ferne natürlich schwer zu sagen. Kann es sein, dass die Pivot-Tabelle einfach nicht (mehr) alle Datensätze aus der Quelltabelle einschließt?
Das könnte mit einer formatierten Tabelle verhindert werden. Damit wäre zumindest sichergestellt, dass immer alle Datensätze in der Pivot-Tabelle automatisch berücksichtigt werden, auch wenn neue dazukommen. Ansonsten fällt mir spontan jetzt auch nichts ein…
Schöne Grüße,
Martin
Hallo Martin,
Ich habe einen schnelleren und einfacheren Weg entdeckt.
Ich habe über die sortieren Funktion einen alphabetisch sortierten dynamischen Array der Spalte erzeugt.
In der Datenüberprüfung nun noch die Adresse des dynamischen Arrays eingeben z.B. =F4# und fertig.
Das war 2016 in Excel bestimmt noch nicht möglich.
Mit freundlichen Grüßen
Martin
Hallo Martin,
ja, das geht heute mit M365 deutlich einfacher. Alle Anwender, die keine dynamischen Arrays nutzen können, müssen leider weiterhin auf die etwas umständliche Variante zurückgreifen.
Schöne Grüße,
Martin