Dieses Forum nutzt Cookies
Dieses Forum verwendet Cookies, um deine Login-Informationen zu speichern, wenn du registriert bist, und deinen letzten Besuch, wenn du es nicht bist. Cookies sind kleine Textdokumente, die auf deinem Computer gespeichert werden. Die von diesem Forum gesetzten Cookies werden nur auf dieser Website verwendet und stellen kein Sicherheitsrisiko dar. Cookies aus diesem Forum speichern auch die spezifischen Themen, die du gelesen hast und wann du zum letzten Mal gelesen hast. Bitte bestätige, ob du diese Cookies akzeptierst oder ablehnst.

Ein Cookie wird in deinem Browser unabhängig von der Wahl gespeichert, um zu verhindern, dass dir diese Frage erneut gestellt wird. Du kannst deine Cookie-Einstellungen jederzeit über den Link in der Fußzeile ändern.

Teilbereich in anderem Arbeitsblatt darstellen
#1
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.


Angehängte Dateien
.xlsx   Teilbereich im anderen Sheet anzeigen.xlsx (Größe: 20,95 KB / Downloads: 6)
Herzliche Grüße aus dem Rheinland
Lucky Joe

[Windows 10, Office 2016]
Antwortento top
#2
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.
Gruß Günter
Misserfolg ist eine Chance, es beim nächsten Mal besser zu machen.
(Henry Ford) www.zitate.net
[-] Folgende(r) 1 Benutzer sagt Danke an WillWissen für diesen Beitrag:
  • LuckyJoe
Antwortento top
#3
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);"")
Gruß Werner
.. , - ...
[-] Folgende(r) 1 Benutzer sagt Danke an neopa für diesen Beitrag:
  • LuckyJoe
Antwortento top
#4
@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!
Herzliche Grüße aus dem Rheinland
Lucky Joe

[Windows 10, Office 2016]
Antwortento top
#5
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)&"";"")
Gruß Werner
.. , - ...
[-] Folgende(r) 1 Benutzer sagt Danke an neopa für diesen Beitrag:
  • LuckyJoe
Antwortento top
#6
... oh wie klein und fein - Danke!
Herzliche Grüße aus dem Rheinland
Lucky Joe

[Windows 10, Office 2016]
Antwortento top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste