Excel INDIREKT dynamischer Zellenbezug?
Hallo,
ich möchte in einer Excel Datei mit mehreren Tabellen pro Zelle jeweils einen bestimmten Wert aus der vorherigen Tabelle ziehen um damit dann weiter zu rechnen.
Mit =INDIREKT($A$36&"!M7:M"&ZEILE(M7)) funktioniert das prinzipiell ganz gut (in A36 sitzt der Name der vorherigen Tabelle & aus der soll dann der Wert aus M7 gezogen werden). Allerdings würde ich gerne die Formel per kopieren auf andere Zellen anwenden. In vertikaler Richtung funktioniert das problemlos, in horizontaler Richtung aber nicht, denn da nimmt die Formel immer den Wert der Nachbarzelle (?).
Vielleicht kennt sich da ja jemand besser aus ^^
3 Antworten
Hallo vovmo,
wenn ich die Aufgabe richtig verstanden habe, müsste Dir folgendes helfen:
Voraussetzung:
- In Zelle A36 des aktuellen Blattes ist der Name der Bezugstabelle (z.B. "Sheet1")
- Die Werte, die Du in das aktive Blatt übernehmen willst, stehen in der Bezugstabelle in Zellen M7 sowie rechts davon und darunter.
- Du möchtest diese Zellwerte in das aktuelle Blatt z.B. in den Zellen B12 sowie entprechend rechts davon und darunter übernehmen.
Lösung: Deinen Ansatz mit dem "INDIREKT" finde ich sehr gut. Damit triffst Du, wie Du gemerkt hast, die eine Zelle oben links des relevanten Bereiches. Um die Zellen darunter und rechts davon durch das übliche Ziehen oder Kopieren zu übernehmen, würde ich "BEREICH.VERSCHIEBEN" verwenden. Damit lautet die Formel:
=OFFSET(INDIRECT($A$36&"!M7");ROW()-12;COLUMN()-2)
oder auf deutsch:
=BEREICH.VERSCHIEBEN(INDIREKT($A$36&"!M7");ZEILE()-12;SPALTE()-2)
Zur Erklärung: Die Verschiebungswerte "-12" für die Zeile und "-2" richten sich nach der obersten linken Zelle, in die die Werte übernommen werden sollen (hier: B12)
Ich hoffe, dies löst Dein Problem.
Wäre es schwierig, wenn der Name der anderen Tabelle direkt in der Formel steht? Dann könntest du einfach BEREICH.VERSCHIEBEN oder INDEX verwenden.
In welcher Zelle steht die Formel?
Statt "M7" könntest du auch
ADRESSE(ZEILE(M7);SPALTE(M7))
verwenden. Dann lässt es sich auch waagerecht verschieben.
Oder besser
=INDIREKT(ADRESSE(ZEILE(M$7);SPALTE(M$7);4;;$A$26)&":"&ADRESSE(ZEILE(M7);SPALTE(M7);4;;$A$26))
Anscheinend kann man über eine Zahl als Index nur in "Makros" auf andere Tabellen zugreifen. Bei einer Internet-Recherche habe ich auch nur die Möglichkeit mit INDIREKT gefunden. ( http://www.pctipp.ch/tipps-tricks/kummerkasten/office/artikel/variabler-bezug-auf-ein-anderes-excel-tabellenblatt-31006/ )
Da "!M7:M" in der Formel als String, also als Text steht, kann sich daran beim Kopieren nichts ändern. $A$36 ist auch absolut gesetzt und kann sich nicht ändern.
Das einzige, was sich ändert ist das ZEILE(M7). DAS ändert sich aber beim runterkopieren (vertikal) und NICHT beim nach rechts kopieren (horizontal).
Also WAS meinst Du mit da nimmt die Formel immer den Wert der Nachbarzelle (?)?
Gib mal zwei Beispiele, wie die Formel sich ändert und was das Ergebnis ist.
P.S.: Dass man mit $M7 die Spalte oder mit M$7 die Zeile festschreiben kann, weißt Du?
Bei meiner Rechnung nimmt die Formel aus irgendeinem Grund (aus dem vorherigen Tabellenblatt) z.B. nicht den Wert aus M7 sondern aus L7, anstatt N7 aus M7 usw.
waagerecht verändert sich die Formel:
=INDIREKT($A$36&"!$M$7:M"&ZEILE(M7)) [Ergebnis richtig]
=INDIREKT($A$36&"!$M$7:M"&ZEILE(L7)) [nimmt Wert aus K7]
=INDIREKT($A$36&"!$M$7:M"&ZEILE(N7)) [nimmt Wert aus M7]
und senkrecht:
....ZEILE(L8) [richtig]
....ZEILE(L9) [richtig]
....ZEILE(L10) [richtig]
Das kann ich so nicht nachvollziehen. Das "M" ist als String festgeschrieben.
Aber was willst Du eigentlich mit
=Tabelle1!M7:M8
oder
=Tabelle1!M7:M10
, das Du Dir da zusammenbaust erreichen.
Ich sehe gerade, dass Excel damit nicht wirklich was anfangen kann und dann die Formelzelle irgendwie mit einbaut. Ist ein bisschen wirr.
Wenn Du nur den Inhalt EINER Zelle abfragen willst, dann mit:
=INDIREKT($A$36&"!$M$"&ZEILE(M7))
Sonst schicke mal die ganze Formel, denn SO macht das - wie gesagt - keinen Sinn.
Die Formel sollte in allen Zellen von L7-L35 & von M7-M30 stehen.
Es wäre eigentlich kein großes Problem den Namen in die Formel zu schreiben, nur müsste dann die Formel für jedes Tabellenblatt angepasst werden (Die Tabellenblätter sind jeweils Monate und die Werte sollen quasi vom Vormonat kopiert werden der eben ohnehin in einer eigenen Zelle steht die ich sonst als Bezug genommen habe).