Excel Hilfsliste und Dropdown ohne Leerzeilen (Excel 2016)

[MatzeR]

Hallo liebe Forenmitglieder,

ich habe ein nicht unbekanntes Problem in Excel. Leider habe ich bei groß Google auch keine passende Lösung gefunden.
Also es geht um folgendes:
In einer Excel Tabelle Namens "Daten" habe ich in den Zellen C5-C38 Namen drin stehen, welche ich in anderen Tabellenblättern für eine DropdownListe als Quelle verwende.
Leider sind zwischendurch immer wieder einige leere Zellen und diese werden auch im Dropdown angezeigt. Da sich die Inhalte ändern brauche ich also (anscheinend) eine dynamische Hilfsliste ohne die Leerzeilen welche ich dann für die Dropdownliste verwende.
Nun zu meinen Problemen, die ich nicht lösen kann:
1. Die Hilfsliste muss in einem anderen Tabellenblatt stehen, da das Tabellenblatt "Daten" auch druckbar sein muss und ich das Layout auch so gestaltet habe.
2. Es darf kein VBA verwendet werden, da man die Datei auch auf mobilen Geräten anschauen können muss. Also sind nur Excel Funktionen nutzbar.

Habt ihr eine Idee wie ich das ganz lösen kann?

Wäre echt super wenn das klappen könnte.

 

[LesGrossman]

Ohne es probiert zu haben: wenn du den Bereich für die in der DropDown-Liste zulässigen Werte ohne Leerzellen an einer anderen Stelle neu darstellen willst: ich würde eine Rangfolge bilden (engl. Funktion rank) und dafür sorgen, dass die leeren Zellen ganz unten stehen - bspw. indem Du die Länge der Zelleninhalte nutzt (len). Danach einfach an anderer Stelle mittels vlookup/sverweis die Werte nach Rang sortiert ausgeben. So würden die leeren Zellen zumindest ganz unten stehen. Ohne VBA sehe ich keinen Weg auch zusätzlich die Länge der DropDown-Liste anzupassen. Ich hoffe es hilft. VG, Les

 

[cumulonimbus8]

1. Die Hilfsliste muss in einem anderen Tabellenblatt stehen, da das Tabellenblatt "Daten" auch druckbar sein muss und ich das Layout auch so gestaltet habe.
Und da ist neben dem Druckbereich kein Plätzchen mehr frei?? Gerade solche Hilfsspalten parkt man j.w.d.

2. Es darf kein VBA verwendet werden, da man die Datei auch auf mobilen Geräten anschauen können muss. Also sind nur Excel Funktionen nutzbar.
Du willst aus Excel eine Datenbank machen - und die müsste man mit VBA nachprogrammieren…

Beantworte dir bitte diese Frage: C5 bis C38 enthält 34 Einträge. Angenommen ich könnte diese ›nach oben hin verdichten‹ - dann muss das DropDown wissen wo Schluss ist. Ergänzt du einen Eintrag muss das DropDown ergo eine Zeile mehr umfassen, aber eben nicht insgesamt 34 wobei die unteren leer sind (aber sehr wohl anwählbar).
Und diese ›Umklammerung‹ kann nur mit VBA automatisch angepasst werden. Damit ist meiner Auffassung nach das was du willst nicht machbar.

CN8

 

[Snooty]

Angenommen ich könnte diese ›nach oben hin verdichten‹ - dann muss das DropDown wissen wo Schluss ist.

Das lässt sich wunderbar mit der Funktion Bereich.Verschieben lösen:

=BEREICH.VERSCHIEBEN($C$5;0;0;ANZAHL2($C$5:$C$38);2)

Mit dieser Formel definiert man einen neuen Namen im Namensmanager, welchen man dann wiederum als Verweis für die Liste angibt.

Idealerweise würde man eben die Daten nach Spalte C sortieren, damit die Leerzeilen unten stehen - dann werden die vom definierten Namen nicht erfasst und die Liste ist genau so lang, wie sie sein soll.

 

[MatzeR]

ich würde eine Rangfolge bilden

Vielen Dank für die Idee Les. Die Rangfolge funktioniert allerdings nur bei Zahlen und nicht bei Text. Oder mache ich was falsch?

Beantworte dir bitte diese Frage: C5 bis C38 enthält 34 Einträge. Angenommen ich könnte diese ›nach oben hin verdichten‹ - dann muss das DropDown wissen wo Schluss ist. Ergänzt du einen Eintrag muss das DropDown ergo eine Zeile mehr umfassen, aber eben nicht insgesamt 34 wobei die unteren leer sind (aber sehr wohl anwählbar).
Und diese ›Umklammerung‹ kann nur mit VBA automatisch angepasst werden. Damit ist meiner Auffassung nach das was du willst nicht machbar.

Damit der Dropdown weiß wo Schluss ist gibt es die Funktion Anzahl 2. Diese beendet die "Sammlung der Daten für den Dropdown" an der ersten leeren Zeile.
Oder man macht aus den eingegebenen Daten ab Excel 2010 eine sogenannte "Tabelle" dann wird der Dropdown auch automatisch erweitert.
Die Frage ist dann noch wie kann man ohne VBA die Daten die in C5 bis C38 stehen "nach oben hin zu verdichten" in einer Hilfsspalte?
Und wenn es ohne VBA nicht geht, dann müssen eben die Leerezeilen im Dropdown drin bleiben.

Das lässt sich wunderbar mit der Funktion Bereich.Verschieben lösen:

Wenn ich die Funktion Bereich verschieben in irgendeine Zelle einfüge kommt nur #WERT!. Also nach deiner Erklärung schätze ich, ich muss das ganze beim Namesmanager für einen Bereich eintragen. Hab ich gemacht und bekomme als Bereich nicht die Zellen C5-C38 sondern C5-C17 und D5-D17. Also schätze ich ich hab es immernoch nicht so richtig kapiert. Kannst du mir noch etwas weiterhelfen bitte?

 

[Snooty]

Wie beschrieben: du erstellst im Namensmanager einen neuen Eintrag mit obiger Funktion. Den Namen, den du dabei vergibst, verwendest du dann wiederum in deiner Dropdown-Liste als Bezug. In der Liste stehen dann alle Einträge aus dem Bereich C5:C35. Leerzeilen ebenso - daher müsste man die ganze Liste nach Spalte C sortieren.

 

[MatzeR]

Wie beschrieben: du erstellst im Namensmanager einen neuen Eintrag mit obiger Funktion. Den Namen, den du dabei vergibst, verwendest du dann wiederum in deiner Dropdown-Liste als Bezug. In der Liste stehen dann alle Einträge aus dem Bereich C5:C35. Leerzeilen ebenso - daher müsste man die ganze Liste nach Spalte C sortieren.

Genau das habe ich getan. Und nun schau dir mal den bereich an, den es in Wirklichkeit erstellt. Irgendwie klappt das nicht. Das Tabellenblatt Daten hat es automatisch eingefügt. Hast du noch eine Idee was der Fehler sein könnte? Funktioniert es bei dir ordentlich?

 

[Snooty]

Sorry, am Ende muss in der Funktion eine 1 stehen, keine 2.

 

[cumulonimbus8]

Damit der Dropdown weiß wo Schluss ist gibt es die Funktion Anzahl 2. Diese beendet die "Sammlung der Daten für den Dropdown" an der ersten leeren Zeile.

Hast du eine Ahnung, wie weit mich DAUs mit einem «an der ersten leeren Zeile» in den Wahnsinn getrieben haben? Weil man mittendrin löschte. Zu dieser Problematik gibt es keine Formellösung.

Falls da also Lücken sind [da Formeln faktisch sofort reagieren könnte es nur eine geben…] muss vorher jede Leerzeile heraussoriert werden, was den Datenbestand verdichtet. Formeln können aber nicht sortieren. Noch mal: Excel ist keine Datenbank. Und erst recht nicht ohne VBA.

CN8

 

[MatzeR]

Sorry, am Ende muss in der Funktion eine 1 stehen, keine 2.

Dann habe ich zwar nur eine Spalte drin. Jedoch nur die Zeilen bis zum zweiten Datensatz. Also scheint das auch nicht so optimal zu sein.

Ich habe noch eine Formel für eine Hilfsliste gefunden: (in die erste Zelle eintragen und dann nach unten ziehen. Funktioniert aber in der Praxis auch nicht)

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

Ergänzung (10. August 2016)

Noch mal: Excel ist keine Datenbank. Und erst recht nicht ohne VBA.

Danke dir. Dann geht es anscheinend nicht ohne VBA. MIT VBA kann ich das ganze aber auch nicht realisieren, da sich die Datei dann nicht auf Mobilgeräten öffnen lässt.
Komisch ist nur, dass es in LibreOffice zum Beispiel ohne Probleme klappt keine Leerzeilen im Dropdown zu haben. Und das sogar ohne eine Formel einzugeben. Aber mit Microsoft wird das wahrscheinlich nix. Danke auf jeden Fall für deinen Rat.