Excel INDIREKT dynamischer Zellenbezug?

3 Antworten

Vom Fragesteller als hilfreich ausgezeichnet

Hallo vovmo,

wenn ich die Aufgabe richtig verstanden habe, müsste Dir folgendes helfen:

Voraussetzung:

  1. In Zelle A36 des aktuellen Blattes ist der Name der Bezugstabelle (z.B. "Sheet1")
  2. Die Werte, die Du in das aktive Blatt übernehmen willst, stehen in der Bezugstabelle in Zellen M7 sowie rechts davon und darunter.
  3. 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.

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?

Woher ich das weiß:Beruf – IT-Administrator (i.R.)

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]

0
@vovmo

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.

0

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))

0

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).

0

Was möchtest Du wissen?