Excel: Dropdown-Liste für Zelle

[R_o_b]

Salute zusammen, hier komme ich mit vermutlich einem Klassiker:

1.)

Ich möchte aus einer lückenhaften Liste von Werten in einem Tabellenblatt eine möglichst lückenfreie Dropdown-Auswahl für eine Zelle erstellen.

Sehe ich das richtig, daß das nur mit einer Hilfsliste geht, die ich zuvor über folgendes Formelungetüm erstelle? (aus einem anderen Forum, noch ungetestet)

=WENN(ZEILE(A1)\>ANZAHL2(A:A);"";INDEX(A:A;KKLEINSTE(WENN(A$1:A$1000"";ZEILE($1:blush:1000));ZEILE(A1))))

2.)

Außerdem soll die Dropdownliste beim Öffnen den obersten Eintrag aktiviert haben. Derzeit erscheint meine (noch mit Leerzeilen versehene) Dropdownliste beim Öffnen immer mit der ersten leeren Zelle aktiviert, die noch dazu ganz oben ist, sodaß man die Dropdown-Zeilen mit Inhalt zunächst gar nicht sieht. Wenn man nicht nach oben scrollt, werden unbedarfte User denken, die Liste sei leer.

Warum ist das so? Und (akademische Nebenfrage): Warum packt Microsoft nicht mal eine Funktion in die Dropdown-Auswahl, in der „leere Zellen ignorieren“ sich so verhält, wie man es intuitiv vermuten würde, daß nämlich die leeren Zellen des Bezugs für die Liste ignoriert werden).

Danke Euch und winkende Grüße
-Rob.

[silex1_a7ada1]

Hallo,

Sehe ich das richtig, daß das nur mit einer Hilfsliste geht,

Ja, in dem Falle schon!

Außerdem soll die Dropdownliste beim Öffnen den obersten
Eintrag aktiviert haben.

Wird schwer.

Warum ist das so?

XL macht nur dass, was Du ihm sagst!

Und (akademische Nebenfrage): Warum packt
Microsoft nicht mal eine Funktion in die Dropdown-Auswahl, in
der „leere Zellen ignorieren“ sich so verhält, wie man es
intuitiv vermuten würde, daß nämlich die leeren Zellen des
Bezugs für die Liste ignoriert werden).

Ist wie beim SVERWEIS(). Warum ist FALSCH gleich RICHTIG???

Mal ne Bsp.-Datei posten im xls-Format, wäre gut!

VG René

[abgemeldet5]

Hallo,
wenn du deine oben stehende !!Matrix-!!Formel in B1 einfügst und runter kopierst wird der Inhalt aus Spalte A lückenlos gelistet.

Hier mal ein Link dazu:
http://www.excelformeln.de/formeln.html?welcher=43

Wenn du jetzt deine Dropdown-Liste mit folgender Formel erzeugst werden die leeren Zellen ignoriert:
=BEREICH.VERSCHIEBEN($B$1;0;0;SUMMENPRODUKT((B:B"")*1))

gruß Holger

[Thomas_Ramel]

Grüezi Rob

Sehe ich das richtig, daß das nur mit einer Hilfsliste geht,
die ich zuvor über folgendes Formelungetüm erstelle? (aus
einem anderen Forum, noch ungetestet)

Warum ungetestet - wir sind hier nicht im Prophylaxe-Studio…

Und (akademische Nebenfrage): Warum packt
Microsoft nicht mal eine Funktion in die Dropdown-Auswahl, in
der „leere Zellen ignorieren“ sich so verhält, wie man es
intuitiv vermuten würde, daß nämlich die leeren Zellen des
Bezugs für die Liste ignoriert werden).

‚Leere Zellen ignorieren‘ bezieht sich nicht auf die Liste (denn nicht immer ist die Gültigkeit über eine Liste definiert), sondern auf die Zelle mit der Gültigkeit selbst - damit legst Du fest ob eine leere Zelle ebenfalls in die Gültigkeitsprüfung einfliesst oder nicht. Letztlich also ob eine leere Zelle erlaubt ist oder nicht.

Mit freundlichen Grüssen

Thomas Ramel

• MVP für MS-Excel -

[R_o_b]

Mit Bezug auf anderes Tabellenblatt
Hallo Holger

Hier mal ein Link dazu:
http://www.excelformeln.de/formeln.html?welcher=43

Wenn du jetzt deine Dropdown-Liste mit folgender Formel
erzeugst werden die leeren Zellen ignoriert:
=BEREICH.VERSCHIEBEN($B$1;0;0;SUMMENPRODUKT((B:B"")*1))

Klasse! Vielen vielen Dank.

Kannst Du mir noch helfen, diese Formel

{=WENN(ZEILE(A1)\>ANZAHL2(A:A);"";INDEX(A:A;KKLEINSTE(WENN(NICHT(ISTLEER(A$1:A$100));ZEILE($1:blush:100));ZEILEN($1:1))))}

so umzuändern, daß ich auf eine Lückenliste in einem anderen Tabellenblatt zugreifen kann? Ich komme noch nicht hinter das Prinzip der Formel und habe daher versucht, vor allen Zellangaben „Blattname!“ zu packen, aber es klappt nicht.Die so erstellte Hilfsliste soll natürlich in einem Hilfs-Blatt untergebracht sein.

Sonntägliche Grüße :o)
-Rob.

[R_o_b]

Salute René,

Sehe ich das richtig, daß das nur mit einer Hilfsliste geht,

Ja, in dem Falle schon!

Außerdem soll die Dropdownliste beim Öffnen den obersten
Eintrag aktiviert haben.

Wird schwer.

Warum ist das so?

XL macht nur dass, was Du ihm sagst!

Okaaaay :o) Und wie sage ich es ihm?

Und (akademische Nebenfrage): Warum packt
Microsoft nicht mal eine Funktion in die Dropdown-Auswahl, in
der „leere Zellen ignorieren“ sich so verhält, wie man es
intuitiv vermuten würde, daß nämlich die leeren Zellen des
Bezugs für die Liste ignoriert werden).

Ist wie beim SVERWEIS(). Warum ist FALSCH gleich RICHTIG???

Mal ne Bsp.-Datei posten im xls-Format, wäre gut!

Mach ich demnächst zurecht und poste den Link.

Grüßken :o)
-Rob.

[R_o_b]

Salute Thomas,

Sehe ich das richtig, daß das nur mit einer Hilfsliste geht,
die ich zuvor über folgendes Formelungetüm erstelle? (aus
einem anderen Forum, noch ungetestet)

Warum ungetestet - wir sind hier nicht im
Prophylaxe-Studio…

Ja, hassja recht. Ich wollte die Frage schon mal posten, hatte da aber noch keinen Zugriff auf eine Testumgebung.

Und (akademische Nebenfrage): Warum packt
Microsoft nicht mal eine Funktion in die Dropdown-Auswahl, in
der „leere Zellen ignorieren“ sich so verhält, wie man es
intuitiv vermuten würde, daß nämlich die leeren Zellen des
Bezugs für die Liste ignoriert werden).

‚Leere Zellen ignorieren‘ bezieht sich nicht auf die Liste
(denn nicht immer ist die Gültigkeit über eine Liste
definiert), sondern auf die Zelle mit der Gültigkeit selbst -
damit legst Du fest ob eine leere Zelle ebenfalls in die
Gültigkeitsprüfung einfliesst oder nicht. Letztlich also ob
eine leere Zelle erlaubt ist oder nicht.

Jaja, das weiß ich doch. Darum ja meine Frage, weshalb die lückenignorierte Listenerstellung nicht zusätzlich als Option angeboten wird? Ich denke, daß das Problem häufig genug vorkommt. Der Aufwand mit Hilfslisten ließe sich dann elegant vermeiden.

Sonntägliche Grüße :o)
-Rob.

[abgemeldet5]

Hallo,
versuche es mal so:
nachfolgende !! Matrix-Formel !! listet den Inhalt der Spalte A aus dem Tabellenblatt 1 lückenlos auf.

=WENN(ZEILE(Tabelle1!A1)>ANZAHL2(Tabelle1!A:A);"";INDEX(Tabelle1!A:A;KKLEINSTE(WENN(NICHT(ISTLEER(Tabelle1!A$1:A$100));ZEILE($1:blush:100));ZEILEN($1:1))))

[Thomas_Ramel]

Grüezi Rob

Kannst Du mir noch helfen, diese Formel

{=WENN(ZEILE(A1)>ANZAHL2(A:A);"";INDEX(A:A;KKLEINSTE(WENN(NICHT
(ISTLEER(A$1:A$100));ZEILE($1:blush:100));ZEILEN($1:1))))}

so umzuändern, daß ich auf eine Lückenliste in einem anderen
Tabellenblatt zugreifen kann?

Nimm mal die folgende Variante und ersetzte dabei ‚Tabelle1‘ durch deinen Blattnamen:

{=WENN(ZEILE(A1)>ANZAHL2(Tabelle1!A:A);"";INDEX(Tabelle1!A:A;KKLEINSTE(WENN(NICHT(ISTLEER(Tabelle1!A$1:A$100));ZEILE($1:blush:100));ZEILEN($1:1))))}

Aber, und das möchte ich hier deutlich sagen, wenn die Matrixformel über grössere Bereiche geht und mehrfach verwendet wird und dann auch noch mehrere Listen so erzeugt werden sollen, dann geht die Performance der Mappe schnell und spürbar in die Knie.
Ev. wäre dann eine Makro-Lösung welche die Daten ohne Duplikate in die Spalte schreibt der effizientere Weg.

Mit freundlichen Grüssen

Thomas Ramel

• MVP für MS-Excel -

[abgemeldet5]

Nachtrag:
die Gültigkeitsprüfung kann normaler weise nicht auf ein anderes Tabellenblatt zugreifen.

Wenn deine Liste also in Tabellenblatt2 Spalte B steht,
erzeuge mit der ersten Formel einen Namen:
Excel2007 >Formeln >Namensmanager
Name: lückenlos
bezieht ich auf
=BEREICH.VERSCHIEBEN(Tabelle2!$B$1;0;0;SUMMENPRODUKT((Tabelle2!B:B"")*1))

Das Dulldownmenü erzeugst du dann mit Liste > Quelle
=lückenlos

Gruß Holger

[R_o_b]

Servus Holger,

versuche es mal so:
nachfolgende !! Matrix-Formel !! listet den Inhalt der Spalte
A aus dem Tabellenblatt 1 lückenlos auf.

=WENN(ZEILE(Tabelle1!A1)>ANZAHL2(Tabelle1!A:A);"";INDEX(Tabelle
1!A:A;KKLEINSTE(WENN(NICHT(ISTLEER(Tabelle1!A$1:A$100));ZEILE($
1:blush:100));ZEILEN($1:1))))

Klasse! Werde ich gleich machen. Ich hatte aber vergessen darauf hinzuweisen, daß die Tabelle nicht die gesamte Spalte umfaßt (die hat diverse Überschriften und geht nur von Zeile 10 bis 500). Muß ich das wie folgt verfassen?

=WENN(ZEILE(Tabelle1!A10)\>ANZAHL2(Tabelle1!A10:A500);"";INDEX(Tabelle1!A10:A500;KKLEINSTE(WENN(NICHT(ISTLEER(Tabelle1!A$10:A$500));ZEILE($1:blush:100));ZEILEN($1:1))))

Ich habs schon mal getestet, aber es funzt so nicht…

Allerbesten Dank schon mal :o)
-Rob.

[abgemeldet5]

Matrixformel OWT
=WENN(ZEILE(A1)>ANZAHL2(Tabelle1!$A$10:blush:A$500);"";INDEX(Tabelle1!$A$10:blush:A$500;KKLEINSTE(WENN(NICHT(ISTLEER(Tabelle1!A$10:A$500));ZEILE($1:blush:100));ZEILEN($1:1))))

[R_o_b]

Sorry: Nachfrage

=WENN(ZEILE(A1)>ANZAHL2(Tabelle1!$A$10:blush:A$500);"";INDEX(Tabelle
1!$A$10:blush:A$500;KKLEINSTE(WENN(NICHT(ISTLEER(Tabelle1!A$10:A$500
));ZEILE($1:blush:100));ZEILEN($1:1))))

Funktioniert allerbestens. Viiiiielen vielen Dank.

Es ist mir sehr unangenehm, aber weil ich in der Tabelle einige Lückenlisten habe, die ich auf einem Hilfs-Blatt unterbringen möchte, hätte ich noch einen Wunsch: Wie muß die Formel aussehen, damit man sie auch an anderer Stelle als A1 beginnen lassen kann?

Bitte entschuldige, daran hätte ich vorher denken müssen… Wenn Dir das zu blöde wird, antworte einfach nicht mehr, das wär’ okay. Ich versuche auch jedesmal die Systematik hinter der Formel nachzuvollziehen, aber irgendwie …

Ich wundere mich auch, daß es hierzu nicht schon was entsprechendes im Netz zu finden gibt, die Problemstellung ist doch eigentlich recht gewöhnlich und sollte ziemlich oft vorkommen!? Ich habe auch alternativ versucht, die Liste mittels Makro aufzubauen: Im direkten Vergleich mit der Matrixformel aber ist das um ein vielfaches langsamer.

Nochmals ein ganz dickes Dankeschön (Sternchen, wo immer es geht ;o) und viele Grüße
-Rob.

[Thomas_Ramel]

Grüezi Rob

=WENN(ZEILE(A1)>ANZAHL2(Tabelle1!$A$10:blush:A$500);"";INDEX(Tabelle
1!$A$10:blush:A$500;KKLEINSTE(WENN(NICHT(ISTLEER(Tabelle1!A$10:A$500
));ZEILE($1:blush:100));ZEILEN($1:1))))

Es ist mir sehr unangenehm, aber weil ich in der Tabelle
einige Lückenlisten habe, die ich auf einem Hilfs-Blatt
unterbringen möchte, hätte ich noch einen Wunsch: Wie muß die
Formel aussehen, damit man sie auch an anderer Stelle als A1
beginnen lassen kann?

Diese Formel kannst Du genau so wie sie hier steht in eine beliebige Zelle kopieren und nach unten ziehen.

Alle Inhalte in den Teilformeln ZEILE() und ZEILEN() sind Steuerwerte die dann beim runterziehen ihren Wert erhöhen.

Mit freundlichen Grüssen

Thomas Ramel

• MVP für MS-Excel -

[abgemeldet5]

=WENN(ZEILE(A1)>ANZAHL2(Tabelle1!$A$10:blush:A$500);"";INDEX(Tabelle
1!$A$10:blush:A$500;KKLEINSTE(WENN(NICHT(ISTLEER(Tabelle1!A$10:A$500
));ZEILE($1:blush:100));ZEILEN($1:1))))

Funktioniert allerbestens. Viiiiielen vielen Dank.

Es ist mir sehr unangenehm, aber weil ich in der Tabelle
einige Lückenlisten habe, die ich auf einem Hilfs-Blatt
unterbringen möchte, hätte ich noch einen Wunsch: Wie muß die
Formel aussehen, damit man sie auch an anderer Stelle als A1
beginnen lassen kann?

Das muss überhaupt nicht unangenehm sein.
Die Formel musst du gar nicht verändern.
Wichtig ist nur der richtige Bezug [Tabelle1!$A$10:blush:A$500]

Mal eine kurzes Beispiel,
>>relativer Bezug 3
A4 4
usw.
Es wird also dort nur ein Zähler erzeugt der in diesem Fall mit der Anzahl an Einträgen im Bereich A10:A500 verglichen wird. Wird der Zähler größer ist das Ergebnis „“ [Leer]
Ähnlich verhält es sich auch mit ZEILE($1:blush:100);ZEILEN($1:1)

Gruß Holger

[R_o_b]

Merci beaucoup
Super.

Vielen Dank & Sternchen :o)
-Rob.

[R_o_b]

Merci encore
Klasse.

Vielen Dank & Sternchen auch für Dich :o)

Grüßken
-Rob.