Liste ohne Leerzeilen aus drei Bereichen

[EasY]

Hallo, 

Ich suche seit langem eine praktikable Formellösung um folgendes Problem zu lösen und hoffe auf eure Hilfe: 

Ich habe drei festgelegte Bereiche in denen Namen stehen. Die Bereiche sind gleich groß und es gibt dort auch leere Felder. 
Nun möchte ich aus den drei Bereichen eine Auflistung an anderer Stelle machen, bei der die leeren Zellen automatisch ignoriert werden.
Mit PQ finde ich es zu umständlich und die Abfrage muss ständig manuell oder per VBA angestoßen werden. 

Gibt es da eine Idee? 

Danke und Gruß

[LCohen]

Du hast die neueste Version mit z.B. ZUSPALTE, womit Du nebenbei Leerzellen eliminieren kannst.

Ansonsten müsste man wissen, was genau wie aussehen soll.

[EasY]

Hi LCohen, 

ich habe vergessen zu erwähnen, dass ich auf der Arbeit "nur" Excel2016 habe... Es sollte auf beiden Versionen laufen. (Die Funktion kannte ich tatsächlich noch gar nicht)

Anbei eine Testdatei. Das Ergebnis soll sich natürlich mit sich ändernden Eingaben in Spalte A automatisch mit ändern.

[J.K.H.]

Hi EasY,

wenn Du mit einer Hilfsspalte leben kannst so:
Schreib in B2 

PHP-Code:

=WENN(A2<>"";ZEILE()-1;" ") 


Danach dann in C2

PHP-Code:

=INDEX($A$2:$A$37;VERGLEICH(KKLEINSTE($B$2:$B$37;ZEILE()-1);$B$2:$B$37;0)) 


Matrix halt anpassen.
Dann sollte es auch in Excel 2016 funktionieren.

Grüße und schönen Advent

[RPP63]

Moin!
Bevor wir eine Lungentransplantation machen, hätte man evtl. erst gar nicht mit dem Rauchen angefangen.

Ich habe drei festgelegte Bereiche in denen Namen stehen.

Ich nehme an, dass sie als Datenbasis für bspw. Datengültigkeit, Liste dienen?
Falls ja, warum dann dieses:

Die Bereiche sind gleich groß und es gibt dort auch leere Felder.

Da die Leerzellen jeweils am Ende des Bereichs sind:
Hast Du schon mal etwas von dynamischen Namen gehört?
Warum überhaupt untereinander in einer Spalte?
Diese gehören in verschiedene Spalten.
Mal als Beispiel für die erste Liste, hier in Spalte F:

	F
1	Meier
2	Müller
3	Schulte
4
5

Formeln → Namensmanager → Neu
Name: Bereich_1
Bezieht sich auf:
=Tabelle1!$F$1:INDEX(Tabelle1!$F:$F;ANZAHL2(Tabelle1!$F:$F))

Nun kannst Du in der Datengültigkeit → Liste → Quelle folgendes eingeben:
=Bereich_1
Der Bereich ist dynamisch.
Vorher:
   

Jetzt erweitere ich den Bereich durch anhängen von Dremel
Die Liste erweitert sich automatisch:
   

Sollte ich bisher richtig liegen, kann man das nächste Problem angehen.

Gruß Ralf

[EasY]

Hey Ralf, 

leider habe ich auf die Struktur - wie so oft - keinen Einfluss. Das ist eine Art Arbeitsplan, der mal mehr und mal weniger gefüllt wird. Deswegen die gleichgroßen Bereiche... Und die Bereiche sind untereinander weil sie verschiedene Schichten eines Tages abbilden. 

@J.K.H
Ich schaue mir das nachher mal an, danke soweit

[RPP63]

Dann ähnlich wie J.K.H, aber ohne Hilfsspalte gemäß
https://www.herber.de/excelformeln/pages...isten.html

C2: =WENN(ZEILE(Y2)>ANZAHL2(A:A);"";INDEX(A:A;KKLEINSTE(WENN(A$2:A$37<>"";ZEILE($2:$37));ZEILE(Y1))))
als Matrixformel abschließen und herunterziehen.

Gruß Ralf