Leere Zellen in Dropdown ignorieren

Forumthread: Leere Zellen in Dropdown ignorieren

14.02.2013 10:50:39

Roland

Hallo zusammen,
ich habe hier im Forum folgenden Eintrag gefunden, der mein Problem, leere Zellen eines definierten Namensbereiches in einer Gültigkeitsliste zu ignorieren, beinahe löst:
(https://www.herber.de/forum/archiv/1256to1260/1256635_Leere_Zellen_in_Dropdown_ignorieren.html)
Mein Problem: in meiner Tabelle, die als Grundlage für den Namensbereich verwendet wird, ist keine Zelle wirklich leer, alle Zellen bestehen aus Formeln und geben einen Wert aus (soll in die Gültigkeitsliste) oder eben nicht (soll in der Gültigkeitsliste ignoriert werden).
Der oben skizzierte Ansatz funktioniert nur dann, wenn die leeren Zellen auch wirklich leer sind und keine Formeln enthalten. Habt Ihr eine Idee, wie ich dennoch eine Gültigkeitsliste erstellen kann, die "leere" Zellen ignoriert?
Vielen Dank!
LG Roland

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Leere Zellen in Dropdown ignorieren

14.02.2013 11:13:36

Hugo

Hallo,
wenn die "leeren" Einträge Leerstrings "" sind und diese ausschließlich am Ende der Liste auftauchen, dann:
Name: MeineListe, Bezug: =$A$1:Index($A:$A;SUMME(N($A$1:$A$1000"")))
Gruß
Hugo

AW: Leere Zellen in Dropdown ignorieren

15.02.2013 14:41:19

Beverly

Hi Roland,
liste in einer Hilfsspalte alle Zellen der Formelspalte lückenlos auf (Formel dafür s. hier: http://www.excelformeln.de/formeln.html?welcher=43) und beziehe deine Namensdefinition auf diese Hilfspalte.
Tabelle1

	A	B	C	D
1	Formelspalte	Hilfsspalte		b
2	a	a
3	b	b
4		c
5	c	d
6	d	e
7		f
8		g
9	e	#ZAHL!
10	f	#ZAHL!
11		#ZAHL!
12	g	#ZAHL!

verwendete Formeln
Zelle	Formel	Bereich
A2	=WENN(Tabelle2!A2="";"";Tabelle2!A2)
B2	{=WENN(ZEILE(A2)>ANZAHL2(A:A);"";INDEX(A:A;KKLEINSTE(WENN(A$1:A$3000<>"";ZEILE($1:$1000));ZEILE(A2))))}	$B$2
A3	=WENN(Tabelle2!A3="";"";Tabelle2!A3)
B3	{=WENN(ZEILE(A3)>ANZAHL2(A:A);"";INDEX(A:A;KKLEINSTE(WENN(A$1:A$3000<>"";ZEILE($1:$1000));ZEILE(A3))))}	$B$3
A4	=WENN(Tabelle2!A4="";"";Tabelle2!A4)
B4	{=WENN(ZEILE(A4)>ANZAHL2(A:A);"";INDEX(A:A;KKLEINSTE(WENN(A$1:A$3000<>"";ZEILE($1:$1000));ZEILE(A4))))}	$B$4
A5	=WENN(Tabelle2!A5="";"";Tabelle2!A5)
B5	{=WENN(ZEILE(A5)>ANZAHL2(A:A);"";INDEX(A:A;KKLEINSTE(WENN(A$1:A$3000<>"";ZEILE($1:$1000));ZEILE(A5))))}	$B$5
A6	=WENN(Tabelle2!A6="";"";Tabelle2!A6)
B6	{=WENN(ZEILE(A6)>ANZAHL2(A:A);"";INDEX(A:A;KKLEINSTE(WENN(A$1:A$3000<>"";ZEILE($1:$1000));ZEILE(A6))))}	$B$6
A7	=WENN(Tabelle2!A7="";"";Tabelle2!A7)
B7	{=WENN(ZEILE(A7)>ANZAHL2(A:A);"";INDEX(A:A;KKLEINSTE(WENN(A$1:A$3000<>"";ZEILE($1:$1000));ZEILE(A7))))}	$B$7
A8	=WENN(Tabelle2!A8="";"";Tabelle2!A8)
B8	{=WENN(ZEILE(A8)>ANZAHL2(A:A);"";INDEX(A:A;KKLEINSTE(WENN(A$1:A$3000<>"";ZEILE($1:$1000));ZEILE(A8))))}	$B$8
A9	=WENN(Tabelle2!A9="";"";Tabelle2!A9)
B9	{=WENN(ZEILE(A9)>ANZAHL2(A:A);"";INDEX(A:A;KKLEINSTE(WENN(A$1:A$3000<>"";ZEILE($1:$1000));ZEILE(A9))))}	$B$9
A10	=WENN(Tabelle2!A10="";"";Tabelle2!A10)
B10	{=WENN(ZEILE(A10)>ANZAHL2(A:A);"";INDEX(A:A;KKLEINSTE(WENN(A$1:A$3000<>"";ZEILE($1:$1000));ZEILE(A10))))}	$B$10
A11	=WENN(Tabelle2!A11="";"";Tabelle2!A11)
B11	{=WENN(ZEILE(A11)>ANZAHL2(A:A);"";INDEX(A:A;KKLEINSTE(WENN(A$1:A$3000<>"";ZEILE($1:$1000));ZEILE(A11))))}	$B$11
A12	=WENN(Tabelle2!A12="";"";Tabelle2!A12)
B12	{=WENN(ZEILE(A12)>ANZAHL2(A:A);"";INDEX(A:A;KKLEINSTE(WENN(A$1:A$3000<>"";ZEILE($1:$1000));ZEILE(A12))))}	$B$12

{} Matrixformel mit Strg+Umschalt+Enter abschließen
Matrixformeln sind durch geschweifte Klammern {} eingeschlossen
Diese Klammern nicht eingeben!!

definierte Namen
Name	Bezieht sich auf	Tabelle	Z1S1-Formel
Liste	=Tabelle1!$B$2:INDEX(Tabelle1!$B$2:$B$12;ANZAHL2(Tabelle1!$B$2:$B$12)-SUMME(ISTFEHLER(Tabelle1!$B$2:$B$12)*1))		=Tabelle1!Z2S2:INDEX(Tabelle1!Z2S2:Z12S2;ANZAHL2(Tabelle1!Z2S2:Z12S2)-SUMME(ISTFEHLER(Tabelle1!Z2S2:Z12S2)*1))

Daten, Gültigkeit
Zelle	Zulassen	Daten	Wert1	Wert2	Leere Zellen ignorieren	Zellendropdown	Titel Eingabemeldung	Eingabemeldung	Titel Fehler	Fehlermeldung
D1	Liste		=Liste		Wahr	Wahr

Tabellendarstellung in Foren Version 5.51

AW: Leere Zellen in Dropdown ignorieren

15.02.2013 17:00:31

Roland

Hallo Hugo, hallo Karin,
erst einmal vielen Dank für Eure super-schnelle Hilfe - ich habe Eure Ansätze beide versucht, noch ohne Erfolg.
Bei beiden Versuchen werden die "leeren" Felder in der Drop Down Liste ausgegeben. Ich füge die Datei einmal bei. Habt Ihr noch einen Hinweis, was ich übersehen habe?
https://www.herber.de/bbs/user/83929.xlsx
Vielen Dank!
LG Roland

AW: Leere Zellen in Dropdown ignorieren

15.02.2013 23:52:28

Beverly

Hi Roland,
Namensdefinition =Konfiguration!$H$3:INDEX(Konfiguration!$H$3:$H$20;ANZAHL2(Konfiguration!$H$3:$H$20)-ANZAHLLEEREZELLEN(Konfiguration!$H$3:$H21)+1)

AW: Leere Zellen in Dropdown ignorieren

16.02.2013 08:07:44

Roland

Hallo Karin,
danke, dass Du dran bleibst :-) Ich habe es mit diesem Ansatz immer noch nicht hinbekommen. Die leeren Zellen werden nach wie vor mit ausgegeben:
https://www.herber.de/bbs/user/83937.xlsx
Vielen Dank!
LG Roland

AW: Leere Zellen in Dropdown ignorieren

16.02.2013 08:23:03

Beverly

Hi Roland,
das Problem ist, dass Excel die Zellbezüge irgendwie immer ändert, wenn sie nicht absolut angegeben sind (das war mit Konfiguration!$H$3:$H21 der Fall, weshalb auch die +1 notwendig war um die korrekte Anzahl an Leerzellen ermitteln zu können). Mit der folgenden Formel sollte es jetzt eigentlich funktioneren:
=Konfiguration!$H$3:INDEX(Konfiguration!$H$3:$H$20;ANZAHL2(Konfiguration!$H$3:$H$20) -ANZAHLLEEREZELLEN(Konfiguration!$H$3:$H$20))

AW: Leere Zellen in Dropdown ignorieren

16.02.2013 09:00:52

Roland

Super, Karin! Damit funktioniert es!
Ganz lieben Dank für Deine tolle Unterstützung!
LG Roland

Infobox / Tutorial

Dropdown-Listen in Excel: Leere Zellen Ignorieren

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Daten vorbereiten: Stelle sicher, dass Deine Daten in einer Spalte organisiert sind. Wenn einige Zellen Formeln enthalten, die leere Werte zurückgeben, müssen diese so bearbeitet werden, dass sie als "leer" erkannt werden.
Hilfsspalte erstellen: Füge eine Hilfsspalte hinzu, die alle "nicht-leeren" Werte auflistet. Verwende dazu die folgende Formel:
```
=WENN(A1<>"";A1;"")
```
Ziehe die Formel bis zur letzten Zeile Deiner Daten.
Namensbereich definieren: Gehe zu Formeln > Namensmanager und erstelle einen neuen Namen. Setze als Bezug:
```
=BEREICH.VERSCHIEBEN(Tabelle1!$B$1;0;0;ANZAHL2(Tabelle1!$B:$B);1)
```
Dies wird alle nicht-leeren Zellen erfassen.
Datenüberprüfung einrichten: Wähle die Zelle aus, in der das Dropdown erscheinen soll. Gehe zu Daten > Datenüberprüfung. Wähle Liste und gib als Quelle den definierten Namen ein.
Leere Zellen ignorieren: Achte darauf, dass die Option Leere Zellen ignorieren aktiviert ist.

Häufige Fehler und Lösungen

Dropdown listet leere Zellen auf: Überprüfe die Formeln in der Hilfsspalte. Wenn sie nicht korrekt sind, könnten leere Strings ("") als Werte erkannt werden. Stelle sicher, dass nur echte Werte ohne Fehler angezeigt werden.
Datenüberprüfung funktioniert nicht: Wenn Du die Option Leere Zellen ignorieren aktiviert hast und es funktioniert nicht, überprüfe die Namensdefinition. Sie sollte exakt die nicht-leeren Zellen umfassen.
Formel wird nicht akzeptiert: Achte darauf, dass Du die Formel als Matrixformel mit Strg + Umschalt + Enter bestätigst.

Alternative Methoden

Daten filtern: Du kannst auch die Filterfunktion in Excel verwenden, um leere Zellen auszublenden, bevor Du ein Dropdown erstellst.
VBA-Makros: Für komplexere Anforderungen kann ein VBA-Makro genutzt werden, um leere Zellen in Dropdown-Listen automatisch zu ignorieren.

Praktische Beispiele

Beispiel 1: Wenn Du eine Liste von Produkten hast, die in einer Spalte stehen, aber einige Produkte aufgrund von Berechnungen nicht verfügbar sind, kannst Du die Hilfsspalte nutzen, um nur die verfügbaren Produkte in der Dropdown-Liste anzuzeigen.
Beispiel 2: Verwende die Funktion INDEX zusammen mit ANZAHL2, um eine Liste ohne leere Zellen zu erstellen:
```
=INDEX(A:A;KLEINSTE(WENN(A$1:A$100<>"";ZEILE(A$1:A$100));ZEILE(1:1)))
```

Tipps für Profis

Verwende benannte Bereiche: Das Erstellen von benannten Bereichen hilft, Deine Formeln übersichtlicher zu gestalten und erleichtert die Verwaltung von Dropdown-Listen.
Dynamische Dropdowns: Nutze die Funktion INDIREKT, um Dropdown-Listen zu erstellen, die sich dynamisch ändern, abhängig von anderen Zellen.
Fehlerbehandlung: Füge Fehlerbehandlungsformeln hinzu, um sicherzustellen, dass Dein Dropdown auch bei unerwarteten Eingaben funktioniert.

FAQ: Häufige Fragen

1. Wie kann ich sicherstellen, dass mein Dropdown keine leeren Zellen enthält? Verwende eine Hilfsspalte mit einer Formel, die leere Werte ignoriert, und definiere den Namensbereich entsprechend.

2. Was kann ich tun, wenn die Dropdown-Liste immer noch leere Zellen anzeigt? Überprüfe die Formeln in Deiner Hilfsspalte und stelle sicher, dass die Option Leere Zellen ignorieren aktiviert ist.

3. Gibt es eine Möglichkeit, die Dropdown-Liste automatisch zu aktualisieren? Ja, indem Du einen dynamischen Namensbereich verwendest, der sich basierend auf den Daten in Deiner Tabelle aktualisiert.

Beliebteste Forumthreads (12 Monate)

Leere Zellen in Dropdown ignorieren

Forumthread: Leere Zellen in Dropdown ignorieren

7
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Infobox / Tutorial

Dropdown-Listen in Excel: Leere Zellen Ignorieren

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

Leere Zellen in Dropdown ignorieren

Forumthread: Leere Zellen in Dropdown ignorieren

7 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

Dropdown-Listen in Excel: Leere Zellen Ignorieren

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

7
Beiträge zum Forumthread

Beiträge zu diesem Forumthread