Nach Kopie Bezüge ändern

[Rudi'S]

Hallo den Helfern und den Lesern,

mein Kassenbuch (fange ich an) ist so aufgebaut, dass für die einzelnen Jahre Tabellen existieren (sollen).
Das ist kein Problem, auch nicht das Erstellen von neuen Jahren (Kopie wird erstellt vor das aktuelle Jahr).
Mein Problem ist in der Zusammenstellung der einzelnen Jahre (zur Gegenüberstellung)
Dort sind in den Spalten die Beträge der einzelnen Kategorien als Bezug zum jeweiligen Jahr hergestellt.
Wenn ich jetzt ein neues Jahr anlege, soll automatisch dann auch in der Zusammenstellung auch der Bezug in der neu erstellten Spalte zu dem neuen Jahr (neuen Tabellenblatt) in den Formeln geändert werden.

Sprich aus:   =WENNFEHLER(SVERWEIS(A20;'2020'!$A$173:$AN$184;40;FALSCH);"") 
soll werden: =WENNFEHLER(SVERWEIS(A20;'2021'!$A$173:$AN$184;40;FALSCH);"")
oder analog bei den anderen Formeln ebenso.

Eine Lösung so konventionell oder per Makro wäre toll. 

Vielleicht hat hier doch jemand einen 'einfachen' Lösungsvorschlag. Danke!

Mfg Rudi

Sorry, finde nicht den Knopf, um eine Datei anzuhängen.

[WillWissen]

Hi Rudi,

Sorry, finde nicht den Knopf, um eine Datei anzuhängen.

lies mal hier: https://www.clever-excel-forum.de/Thread...ng-stellen

[Oldiefan]

Hallo Rudi!

Ich würde da mit Indirekt arbeiten. Das funktioniert gut, weil die Datei ohnehin offen ist, wenn du daran arbeitest. Definiere zuerst eine Zelle mit dem Namen Jahr und schreibe die aktuelle Jahreszahl rein. Aus =WENNFEHLER(SVERWEIS(A20;'2021'!$A$173:$AN$184;40;FALSCH);"") wird dann =WENNFEHLER(SVERWEIS(A20;Indirekt("'"&Jahr&"'!$A$173:$AN$184");40;FALSCH);"")

Liebe Grüße
Alex

[Rudi'S]

Hallo Alex,
das war die Lösung. Danke!

Es kommt eben auf die richtige Schreibweise an.


Aber trotzdem mal die Ergänzungsfrage: Wie kann ich das über eine Matrix lösen, da ja sowieso das neue Jahr darüber angelegt wird. 
Stelle es mir dann so vor, neues Jahr (neue Tabelle) wird angelegt und dann sofort in dieser Übersicht das neue Jahr dann rechts neben dem alten in der Spalte angelegt (also copy nach rechts - altes Jahr finden und ersetzen). Ansonsten kein Problem, ein bißchen Handarbeit mit der vorgetragenen Formellösung ist auch machbar.

Gibt es keine solche Lösung, dann wäre die Anfrage hiermit erledigt.
Dank nochmal.

Gruß Rudi

[atilla]

Hallo,

Indirekt() ist ein Ressourcen -fresser, dass solltest Du wissen.
Es kommt darauf an wie viel Du einsetzt.

Wenn ich so ein Problem hatte, wie Du, dann habe ich die Excel Funktion suchen ersetzen bemüht.
Einfach den Bereich markieren und die Option Suchen in Formeln genutzt.

[Oldiefan]

Aber trotzdem mal die Ergänzungsfrage: Wie kann ich das über eine Matrix lösen, da ja sowieso das neue Jahr darüber angelegt wird. 
Stelle es mir dann so vor, neues Jahr (neue Tabelle) wird angelegt und dann sofort in dieser Übersicht das neue Jahr dann rechts neben dem alten in der Spalte angelegt (also copy nach rechts - altes Jahr finden und ersetzen). Ansonsten kein Problem, ein bißchen Handarbeit mit der vorgetragenen Formellösung ist auch machbar.

Hallo Rudi!

Auch das geht, wenn auch mit kleinen Tricks aus früheren Zeiten. Definiere zuerst einen Namen Jahre mit Bezug auf folgende Formel: =ARBEITSMAPPE.ZUORDNEN(1+0*JETZT()).
Jetzt schreibst du in die erste Zelle deiner Matrix =WENN(ZEILE(A1)>ANZAHL2(Jahre);"";INDEX(Jahre;ZEILE(A1))&"'!A1";TEIL(INDEX(Jahre;ZEILE(A1));FINDEN("]";INDEX(Jahre;ZEILE(A1)))+1;31)) und ziehst diese Formel nach rechts (aber bitte etwas weiter als deine Blattanzahl ist, damit du das nicht jedes Jahr neu machen musst). 
Im nächsten Schritt kannst du meine Formel von oben hernehmen und Jahr durch die erste Matrixzelle ersetzen, rüberziehen und fertig.

Liebe Grüße
Alex

[Fred11]

Hallo Rudi,

ich würde es so machen:

1. 
Die Spaltenüberschriften in Zeile 2 für die Ergebnisse der einzelnen Jahre (Zellen E2, D2, usw) genauso benenn wie die entsprechenden Tabellenblätter heissen, also 2020, 2021, usw

2. Formel mit INDIREKT und Bezug auf die Spaltenüberschrift ($-Zeichen beachten)

=WENNFEHLER(SVERWEIS(A19;INDIREKT("'"&D$2&"'!$A$173:$AN$184");40;FALSCH);"")

Diese Formel kannst du dann nach rechts ziehen und mithilfe der INDIREKT-Funktion greift die Formel dann immer auf das richtige Tabellenblatt zu.

Gruss
Fred

[RPP63]

Moin!
Jahresblätter waren, sind und bleiben kontraproduktiv!
Ein Tabellenblatt hat 2^20 aka 1.048.576 Zeilen.
Dies dürfte für ein paar Jahre reichen, die man dann einfach filtern kann.

Eine Jahres-Auswertung erstellt man dann einfach per Formel (TEILERGEBNIS, SUMMEWENNS, …)
(oder besser per Pivot-Table)

Gruß Ralf

[Rudi'S]

Dank an Fred und Alex!

Hei, 
hab die kürzere Lösung vorgezogen. 
Mal sehen, was mir die VBA-Lösung bringt (mit Sicherheit weniger Handarbeit).

Ralf: Im Prinzip gebe ich dir Recht, aber in dem besonderen Fall wäre das schlecht durchführbar.

Gruß Rudi