Teilbereich in anderem Arbeitsblatt darstellen

[LuckyJoe]

Hallo zusammen,

im Moment stehe ich irgendwie auf dem Schlauch mit einer Formel: Ich habe ein Arbeitsblatt "Teilnehmer" mit Teilnehmerdaten. Den Bereich mit den Adressen habe ich als "TNDaten" benannt. Unterhalb dieses Bereichs steht noch weiterer (unentbehrlicher) Text. Die Liste mit den Teilnehmern ist je nach Verwendung unterschiedlich lang (Zeilen).

Auf einem zweiten Arbeitsblatt möchte ich nun alle Namen dieser Adressliste angezeigt bekommen und zwar ab Zeile 6, da sich vorher noch Text befindet. Hier ist Platz für max. 30 Teilnehmer. Das Layout dieses Blattes ist fest, d.h. es sind immer 30 Teilnehmerplätze vorhanden. Als Formel habe ich nun verwendet:

Code:

=WENN(ISTLEER(BEREICH.VERSCHIEBEN(TNDaten;ZEILE()-6;;1;1));"";BEREICH.VERSCHIEBEN(TNDaten;ZEILE()-6;;1;1))

Das funktioniert soweit auch gut, allerdings wird mir so auch der Text unterhalb der Adressliste angezeigt, was ich vermeiden möchte.

Frage: Wie muss ich die Formel auf dem Blatt "Liste" abändern, damit ich in "B6:B35" nur die Daten aus dem Bereich "TNDaten" erhalte (die restlichen Zellen sollen dann leer bleiben)?

Die Namensliste ist übrigens immer alphabetisch sortiert, falls das ggf. für eine andere Formel eine Rolle spielt.

Danke schon mal im Voraus.

[WillWissen]

Hi Joe,

in deiner Teilnehmerliste ist nur Platz für max. 22 Teilnehmer (die drei Beispieltexte wären Teilnehmer 23, 24 und 25). Ist das so gewollt? Dann wird der Text auf der Liste mit 30 Teilnehmern immer auftauchen. Ich denke, da müsstest du den Hebel ansetzen.

[neopa]

Hallo Joe,

es geht viel einfacher und auch ohne die volatile Funktion BEREICH.VERSCHIEBEN().

Nachfolgend auch gleich inklusive dem Vornamen (wenn letzterer nicht gewünscht sein sollte, einfach den entsprechenden Formelteil noch weglassen):

Code:

=WENNFEHLER(INDEX(TNDaten;ZEILE()-5;1)&", "&INDEX(TNDaten;ZEILE()-5;2);"")

[LuckyJoe]

@Günter: Danke für deine Antwort; die Teilnehmerliste kann bis zu 30 TN umfassen. Ich habe im Beispiel nur 16 TN erfasst, 5 TN-Plätze frei gelassen und die TN-Liste nach der 21. Zeile "abgeschnitten". Das kann in der Praxis so vorkommen.

@Werner: sehr schön und fast perfekt: Kürze ich die Formel nur auf die Nachnamen (nur die möchte ich verwenden):

Code:

=WENNFEHLER(INDEX(TNDaten;ZEILE()-5;1);"")

... dann stimmt alles, bis auf die leeren Zeilen innerhalb der TNDaten, die mit "0" dargestellt werden. Alles darunter ist dann tatsächlich leer. Ich habe die Formel jetzt so abgeändert:

Code:

=WENNFEHLER(WENN(INDEX(TNDaten;ZEILE()-5;1)=0;"";INDEX(TNDaten;ZEILE()-5;1));"")

... und sie funktioniert. Danke!

[neopa]

Hallo Joe,

auch das geht einfacher. Zwei Lösungsvarianten:

1. Variante; Formel: 

Code:

=WENNFEHLER(INDEX(TNDaten;ZEILE()-5;1);"")

und den Zellen das benutzerdefinierte Zahlenformat:

Code:

0;;

zuweisen.

2. Variante ohne benutzerdefiniertes Zahlenformat und die Formel dann aber so:

Code:

=WENNFEHLER(INDEX(TNDaten;ZEILE()-5;1)&"";"")

[LuckyJoe]

... oh wie klein und fein - Danke!