Excel jede n. Zelle auslesen Formel zum runterziehen.

[Amanar]

Hallo,
ich versuche grade eine Excel Formel zu finden, mit der ich jede n. Zelle aus einer anderen Seite auslesen kann.
ich habe Bereits eine Formel welche funktioniert, wenn man von ganz oben anfängt: =INDIREKT("Messschieber!B"&ZEILE(Messschieber!B1)*22)
Mit der Formel funktioniert es jede 22. Zelle aus MessschieberB auszulesen und ich kann sie auch einfach nach unten weiterziehen.
Jetzt brauche ich jedoch jede 22. Zelle aus MessschieberB ab der 38. Zelle.
Ich habe es schon mit Index versucht, jedoch funktioniert Index anscheinend nicht seitenübergreifend.
Mit dieser Formel habe ich rumprobiert =INDEX(Messschieber!B38:Messschieber!B38;ZEILE(A1)*22), jedoch sobald ich den Bezug auf die Seite Messschieber beziehe, bekomme ich einen Bezugsfehler.

Das ganze soll ein Deckblatt werden, auf dem die Daten aus den anderen Blättern automatisch erfasst werden können, indem man die Formel nach unten zieht.

Gibt es vielleicht einen Trick, mit welchem man die Indirekt Formel erweitern kann, sodass ich auch Zeilen von weiter unten als Startpunkt nutzen kann?

 

[kartoffelpü]

=INDIREKT("Messschieber!B"&ZEILE(Messschieber!B1)*22)

Ich habe die Funktion "INDIREKT" noch nie genutzt, aber müsste es nicht ausreichen, wenn du einfach statt Messschieber!B1 Messschieber!B38 nimmst?

 

[steve1da]

Index() funktioniert ganz hervorragend über mehrere Blätter.
https://www.herber.de/excelformeln/pages/Jede_Xte_Zeile_aus_einer_Liste_uebernehmen.html

 

[Amanar]

Ich habe die Funktion "INDIREKT" noch nie genutzt, aber müsste es nicht ausreichen, wenn du einfach statt Messschieber!B1 Messschieber!B38 nimmst?

Das war auch meine erste Überlegung, jedoch gibt mir Excel eine 0 aus wenn ich es so mache.

Ergänzung (6. September 2021)

Index() funktioniert ganz hervorragend über mehrere Blätter.
https://www.herber.de/excelformeln/pages/Jede_Xte_Zeile_aus_einer_Liste_uebernehmen.html

Das was du mir geschickt hast ist Index auf einer Seite, ich will aber mehrere Seiten miteinander verknüpfen.

 

[steve1da]

Da kommt natürlich noch der Tabellenname vor den Bereich so wie du es bei deiner Formel auch gemacht hast.

 

[SKu]

Bei solchen Anfragen ist es immer ratsam das Beispiel als Excel-Datei beizufügen.

Bei der Index-Formel fällt mir allerdings direkt ein Fehler auf. Wenn du dich auf eine Matrix beziehst, dann muss diese absolut sein. Ansonsten würdest du mit dem Herunterziehen auch die Matrix verändern. Das liefert dir ein falsches Ergebnis oder eben #NV. Auch hast du keinen Spaltenbezug.

Was ist denn ZEILE(A1)? Das ist doch immer 22 in deinem Fall.

 

[steve1da]

Ich glaube es ist eher sowas gemeint:
=INDEX(Messschieber!$B$38:$B$831;ZEILE(A1)*22-21)

 

[Amanar]

Bei solchen Anfragen ist es immer ratsam das Beispiel als Excel-Datei beizufügen.

Bei der Index-Formel fällt mir allerdings direkt ein Fehler auf. Wenn du dich auf eine Matrix beziehst, dann muss diese absolut sein. Ansonsten würdest du mit dem Herunterziehen auch die Matrix verändern. Das liefert dir ein falsches Ergebnis oder eben #NV. Auch hast du keinen Spaltenbezug.

Was ist denn ZEILE(A1)? Das ist doch immer 22 in deinem Fall.

Die Datei enthält leider vertrauliche Daten, darum kann ich die vorerst noch nicht hochladen.
Ich habe zuvor noch sehr wenig mit Excel gearbeitet, darum sind die ganzen begriffe, wie Matrix usw. für mich noch ziemlich schwer zuzuordnen. Ich schaue mir meistens eine Formel an probiere sie aus und leite dann davon aber wie sie funktioniert. Die Matrix Formel oben war auch nur ein halbes Experiment. ich versuche gleich mal eine Beispieldatei zu erstellen damit ihr euch austoben könnt. Danke für die schnellen Antworten.

Ergänzung (6. September 2021)

Ich glaube es ist eher sowas gemeint:
=INDEX(Messschieber!$B$38:$B$831;ZEILE(A1)*22-21)

Jawoll danke dir, die Formel funktioniert perfekt, kannst du mir eventuell eine Erklärung dazu schreiben, damit ich sie nachvollziehen und in Zukunft auch selbst verwenden kann?

 

[steve1da]

Du musst bei Index() immer angeben, in welchem Bereich die Daten stehen, also B38 bis B irgendwas. Damit sich das nicht beim Ziehen der Formel verändert wird alles was fix sein muss mit $-Zeichen fixiert.
Zeile(A1) fungiert hier als Zähler der 1, 2, 3, 4.... ausgibt wenn man die Formel nach unten zieht. Der Rest ist einfach Mathe. 1*22-21 ergibt 1, also der 1. Wert aus dem Index-Bereich. 2*22-21 ergibt 23, also der 23. Wert aus dem Index-Bereich, 3*22-21 ergibt 45, also der 45. Eintrag aus dem Index-Bereich, usw.

 

[SKu]

Also ich verstehe das jetzt so final:

Er liefert den Rückgabewert für Zeile 38 und danach immer +22

 

[Amanar]

Also ich verstehe das jetzt so final:

Er liefert den Rückgabewert für Zeile 38 und danach immer +22

Ja so sollte es sein. Danke für eure Hilfe und noch eine schöne Woche.

Ergänzung (6. September 2021)

Du musst bei Index() immer angeben, in welchem Bereich die Daten stehen, also B38 bis B irgendwas. Damit sich das nicht beim Ziehen der Formel verändert wird alles was fix sein muss mit $-Zeichen fixiert.
Zeile(A1) fungiert hier als Zähler der 1, 2, 3, 4.... ausgibt wenn man die Formel nach unten zieht. Der Rest ist einfach Mathe. 1*22-21 ergibt 1, also der 1. Wert aus dem Index-Bereich. 2*22-21 ergibt 23, also der 23. Wert aus dem Index-Bereich, 3*22-21 ergibt 45, also der 45. Eintrag aus dem Index-Bereich, usw.

Ist auch B38 bis B unendlich möglich?

 

[steve1da]

Ist auch B38 bis B unendlich möglich?

Na klar (wobei es unendlich in Excel nicht gibt).

 

[Amanar]

Na klar (wobei es unendlich in Excel nicht gibt).

Naja in dem Sinne wär es dann halt die ganze Spalte B, das wäre bei mir hilfreich da ich die Tabelle immer länger machen werde und wenn ich dann die ganze Spalte anwählen kann, muss ich nichts mehr an der Formel machen. Gibt es einen Parameter für die ganze Spalte B?

 

[steve1da]

Das wäre dann B:B, aber damit erhältst du dann nicht mehr die Liste die du möchtest, weil du nicht jeden 22. Eintrag ab B1 haben willst, sondern ab B38. Excel hat 1.048.576 Zeilen, wirst du die denn alle brauchen? Ansonsten schreib doch $B$38:$B$1000000

 

[SKu]

Die Excel, die ich hochgeladen habe, liefert aber ebene die Alternative mit B:B als Matrix.

 

[steve1da]

Die Excel, die ich hochgeladen habe, liefert aber ebene die Alternative mit B:B als Matrix.

Das stimmt. Aber eben auch eine Hilfsspalte wo die Formel auf Vorrat liegen muss.

 

[SKu]

Das stimmt. Aber eben auch eine Hilfsspalte wo die Formel auf Vorrat liegen muss.

Die Hilfsspalte mit ROW() braucht er auch im anderen Fall.

 

[steve1da]

???
Also ich ziehe eine Formel nach unten, du zwei.

 

[SKu]

Naja, ich ziehe die eine Formel einmal am Anfang nach unten. Die kann ja einmalig bis Zeile 1.000 ziehen z.B.
Hilfreich wäre der Aufbau des Messzeugtabelle. Da wird ja mit Sicherheit eine Spaltenüberschrift pro Messzeug existieren. Dann könnte man eben auch über die Spaltenüberschrift+1 den Wert erhalten, solange die Überschrift eindeutig ist.

 

[steve1da]

Je nach Datenmenge sollte man so wenig Formeln wie möglich verwenden. Funktioniert natürlich trotzdem ;-)